end of string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

end of string

Author

Topic

ramsesiitr
Starting Member

22 Posts

Posted - 2009-01-27 : 07:26:27

Hi all,
I have a question, might be simple enough.


declare @string nvarchar(255)
set @string='Hello World'
declare @i tinyint
set @i=1

while substring(@string,@i,1) is not null  /*??????*/
begin
  select substring(@string,@i,1)
  set @i=@i+1
end

How to stop this loop?
Any suggestion please.
Thanks

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-01-27 : 07:30:04

quote:
Originally posted by ramsesiitr

Hi all,
I have a question, might be simple enough.

declare @string nvarchar(255)
set @string='Hello World'
declare @i tinyint
set @i=1

while @i < = len(@string) and @i is not null /*??????*/
begin
select substring(@string,@i,1)
set @i=@i+1
end

How to stop this loop?
Any suggestion please.
Thanks

Karthik

ramsesiitr
Starting Member

22 Posts

Posted - 2009-01-27 : 07:32:35

Thanks for the reply. I wonder, if there is any way to determine the end of string in sql?

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-01-27 : 07:32:36

what do you want as the output?

where do you want to stop the loop?

Karthik

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-01-27 : 07:34:49

quote:
Originally posted by ramsesiitr

Thanks for the reply. I wonder, if there is any way to determine the end of string in sql?

len() function returns the length of the string so it determines the end of string in sql

Karthik

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-27 : 08:15:56

for splitting string use this query than the while loop

declare @str1 varchar(max)
set @str1= 'Hello World'

SELECT
SUBSTRING(@str1,charindex(' ',@str1,v.number),abs(charindex(' ',@str1,charindex(' ',@str1,v.number)+1)-charindex(' ',@str1,v.number)))as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND v.number <= len(@str1)
AND substring(' ' + @str1, v.number, 1) = ' '

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 09:03:43

quote:
Originally posted by bklr

for splitting string use this query than the while loop

declare @str1 varchar(max)
set @str1= 'Hello World'

SELECT
SUBSTRING(@str1,charindex(' ',@str1,v.number),abs(charindex(' ',@str1,charindex(' ',@str1,v.number)+1)-charindex(' ',@str1,v.number)))as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND v.number <= len(@str1)
AND substring(' ' + @str1, v.number, 1) = ' '

this is not a very scalable solution as it deepends on number of records in master..spt_values and wont work for long strings.


;With Str_CTE(StrVal,Residue)
AS
(SELECT SUBSTRING(@String,1,1),SUBSTRING(@String,2,LEN(@String))
UNION ALL
SELECT SUBSTRING(Residue,1,1),SUBSTRING(Residue,2,LEN(Residue))
FROM Str_CTE
WHERE SUBSTRING(Residue,2,LEN(Residue)) IS NULL
)
SELECT StrVal FROM Str_val
OPTION (MAXRECURSION 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources