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
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
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
You might find Split A String By Using A Number Table useful.
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.
