June 26, 2008 at 2:54 am
Hi All,
Given a string say "Tata Consultancy Limited" how will i get the position of the blank spaces in the string.
Thanks,
Ahmad
Regards,
[font="Verdana"]Sqlfrenzy[/font]
June 26, 2008 at 3:30 am
Use;
CHARINDEX ( expression1 ,expression2 [ , start_location ] )
Check BOL for more deails.
Also have a look at this great article on here, on how to use this to step through strings.
June 26, 2008 at 3:35 am
thnks man..
Regards,
[font="Verdana"]Sqlfrenzy[/font]
June 26, 2008 at 4:35 am
If you like to find all the ocurrences of one char try this:
DECLARE @String varchar(500)
DECLARE @Find char(1)
DECLARE @Pos integer
DECLARE @Position integer
SET @String = 'Tata Consultancy Limited'
SET @Find = 'n'
SET @Pos = -1
SET @Position = 0
WHILE @Pos <> 0
BEGIN
PRINT @String
SET @Pos = CHARINDEX (@Find, @String)
IF @Pos <> 0
BEGIN
SET @Position = @Position + @Pos
PRINT @Position
SET @String = SUBSTRING(@String, @Pos+1, LEN(@String) - @Pos)
END
END
You can make an UDF and return the concatenation of the positions, or return a TABLE whith as many rows as occurrences of the char.
June 26, 2008 at 4:40 am
gud one thanks...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
June 26, 2008 at 5:23 am
Ahmad,
Try out this one,which is very faster than Loop.
Declare @String varchar(40)
Select @String = 'Tata Consultancy Limited'
select N,substring(@String,N,1)
from Tally
where N <= len(@String)
and substring(@String,N,1) = ''
Output:
N
--------
5
17
If you want to know more about,I would suggest you to read the below article about 'Tally' table.
http://qa.sqlservercentral.com/articles/TSQL/62867/
karthik
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply