How can I parse a string in sql?

Question

Hi I am passing a string in a stored procedure (sql Server 2000) like this "123456788997877" (please note I dont have delimited pipes etc...)

I need to do some calculations for each number in the string.

How do I loop each number in a given string and do something about it?

Thanks a lot

Answer 1

You have string functions in TSQL. use them to cut the string as you want, use convert to parse the parts that you want to whatever you want.

Answer 2

You might find Split A String By Using A Number Table useful.

Answer 3

You can try something like this

DECLARE @String VARCHAR(MAX)

SELECT @String = '123456788997877'

DECLARE @Pos INT

SELECT @Pos = 1

WHILE @Pos < LEN(@String)
BEGIN
    DECLARE @Current VARCHAR(1)
    SET @Current = SUBSTRING(@String, @Pos, 1)
    PRINT @Current
    SET @Pos = @Pos + 1
END

ALSO SqlServer 2008 allows

SET @Pos += 1

Answer 4

You can even go ahead with the help of a number table

declare @str varchar(100)
set @str = '123456788997877'

--Build a number table
declare @tblNumber table(num int)
insert into @tblNumber values(1)
insert into @tblNumber values(2)
insert into @tblNumber values(3)
insert into @tblNumber values(4)
insert into @tblNumber values(5)
insert into @tblNumber values(6)
insert into @tblNumber values(7)
insert into @tblNumber values(8)
insert into @tblNumber values(9)
insert into @tblNumber values(10)
insert into @tblNumber values(11)
insert into @tblNumber values(12)
insert into @tblNumber values(13)
insert into @tblNumber values(14)
insert into @tblNumber values(15)

select IndiChars = substring(@str,num,1) from @tblNumber

IndiChars

1
2
3
4
5
6
7
8
8
9
9
7
8
7
7