Splitting the string to insert to the fields.

[nikscrasher]

Hi,
I need to parse this one long text string into 3 distinct columns: serialno, denomination, cardtype to insert to database.

Here's my example string:
vchr_Card =
'0000000001-0000000010,100,sb;
0000000015,150,sp;
0000000127,10,sa;
0000000128-0000000135,15,sc;
0000000136-0000000140,20,st;
0000000180,100,si;
0000000010,500,sq;
0000000015,150,sm;
0000000200-0000000210,1000,sy;
0000000190,350,sx;'

Any ideas anyone? any good scripts out there that can help me to parse the long text string?

Thank you in advance..

 

[bboffin]

Try something like this. It's untested but will probably be fairly close to what you want. I have assumed that you want each set of details to be inserted as a separate record.

-- assume long text is in @Card

DECLARE @CardLine varchar(100)
DECLARE @Length int
DECLARE @SerialNo varchar(100)
DECLARE @Denomination varchar(10)
DECLARE @CardType varchar(10)
DECLARE @Pos int, @Pos2 int

/* this loop works on the basis of stripping off the first set of details (up to the first semicolon) 
    and then removing this text from @Card, Thus when @Card has a length of zero the loop is done.
    It also assumes that there are no other characters either side of the ; and , separators */
WHILE LEN(@Card) > 0
begin
    -- find the length of the first set of details
    set @Length = charindex(';', @Card) - 1
    -- store it in @Cardline
    set @Cardline = LEFT(@Card, @Length)
    -- now remove this text from @Card
    set @Card = replace(@Card, @Cardline + ';', '')
    -- split @Cardline into three fields
    set @Pos = charindex(',', @CardLine)
    set @SerialNo = substring(@CardLine, 1, @Pos - 1)
    set @Pos2 = charindex(',', @CardLine, @Pos + 1)
    set @Denomination = substring(@Cardline, @Pos + 1, @Pos1 - 1)
    set @CardType = substring(@Cardline, @Pos2 + 1, len(@Cardline) - @Pos2 - 1)
    -- now insert the data into the table ( the ... represents any other columns you may be inserting)
    INSERT INTO MyTable (... , SerialNo, Denomination, CardType) 
        VALUES (... , @SerialNo, @Denomination, @CardType)
    -- do any error checking here
    .
    .
    .
end

Bob Boffin

 

[gmmastros]

First, create the SplitString UDF.

CREATE Function dbo.SplitString(@IDs Varchar(100), @CharToSplit Char(1)) 
Returns @Tbl_IDs Table (Data VarChar(100)) As 

Begin 
 -- Append comma
 Set @IDs = @IDs + @CharToSplit
 -- Indexes to keep the position of searching
 Declare @Pos1 Int
 Declare @pos2 Int
 
 -- Start from first character 
 Set @Pos1=1
 Set @Pos2=1

 While @Pos1<Len(@IDs)
 Begin
 Set @Pos1 = CharIndex(@CharToSplit,@IDs,@Pos1)
 Insert @Tbl_IDs Select Substring(@IDs,@Pos2,@Pos1-@Pos2) 
 -- Go to next non comma character
 Set @Pos2=@Pos1+1
 -- Search from the next charcater
 Set @Pos1 = @Pos1+1
 End 
 Return
End

Then you can use it to create a table variable, splitting on the semicolons. Once that is done, a series of updates while searching on the commas should do the trick.

DECLARE @vchr_Card VarChar(1000)

Set @vchr_Card = '0000000001-0000000010,100,sb;0000000015,150,sp;0000000127,10,sa;0000000128-0000000135,15,sc;0000000136-0000000140,20,st;0000000180,100,si;0000000010,500,sq;0000000015,150,sm;0000000200-0000000210,1000,sy;0000000190,350,sx;'

Declare @Temp 
Table	(
		Original VarChar(100),
		SerialNumber VarChar(100),
		Denomination VarChar(100),
		CardType VarChar(100)
		)

Insert Into @Temp(Original)
select FieldName As Original From dbo.SplitString(@vchr_Card, ';')

Update	@Temp
Set		SerialNumber = Left(Original, CharIndex(',', Original) - 1)

Update 	@Temp
Set		Original = Replace(Original, SerialNumber + ',', '')

Update 	@Temp
Set		Denomination = Left(Original, CharIndex(',', Original)-1)

Update 	@Temp
Set		Original = Replace(Original, Denomination + ',', '')

Update 	@Temp
Set		CardType = Original

Select 	SerialNumber, Denomination, CardType From @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DrSql]

Also you can use substring..

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.