How to do split on a 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)

How to do split on a string

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-07-30 : 12:40:30

How can i split the string with spaces but, if there are any strings in between double quotes, they need to be exculded
example: wipro websphere deloitte "banner configurations" cognos

when i do the split, is it possible to first remove the "banner configurations"

and do the split on the rest where spaces are. the result should be:
"banner configurations"
wipro
websphere
deloitte
cognos

I am trying to do a multi string word searches, takes care including teh whole string which is within double quotes.

Thank you very much for the helpful info.

PavanKK
Starting Member

32 Posts

Posted - 2010-08-03 : 02:08:23

Cplusplus,

try this. you can acheieve the same using while loop also.

-----------

DECLARE @String VARCHAR(200)
SET @String = '"Test Test" wipro websphere deloitte "banner configurations" cognos'

;WITH CteQuotes AS
(
SELECT SUBSTRING(@String,CHARINDEX(' "',' '+@String,1),CHARINDEX('" ',@String+' ',1)) Split
,SUBSTRING(@String,CHARINDEX('" ',@String+' ',1)+1,LEN(@String)) Remaining

UNION ALL

SELECT SUBSTRING(Remaining,CHARINDEX(' "',' '+Remaining,1),CHARINDEX('" ',Remaining+' ',1)-CHARINDEX(' "',' '+Remaining,1)+1)
,SUBSTRING(Remaining,CHARINDEX('" ',Remaining+' ',1)+1,LEN(Remaining))
FROM CteQuotes
WHERE CHARINDEX('" ',Remaining+' ',1) >0
),

Cte AS
(
SELECT SUBSTRING(@String,1,CHARINDEX(' ',@String,1)-1) Split
,SUBSTRING(@String,CHARINDEX(' ',@String,1)+1,LEN(@String)) Remaining

UNION ALL

SELECT SUBSTRING(Remaining,1,CHARINDEX(' ',Remaining+' ',1)-1)
,SUBSTRING(Remaining,CHARINDEX(' ',Remaining+' ',1)+1,LEN(Remaining))
FROM Cte
WHERE LEN(Remaining) <> 0
)

SELECT Split FROM CteQuotes
UNION ALL
SELECT Split FROM Cte
WHERE CHARINDEX('"',Split,1)=0

------------

KK :)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-08-03 : 11:39:39

something like this might help also


declare @bootleg varchar(2000)

set @bootleg= 'websphere deloitte "banner configurations" cognos'

SELECT LEN(@bootleg)
SELECT CHARINDEX('"',@bootleg,0)-1
SELECT CHARINDEX('"',REVERSE(@bootleg),0)-1

SELECT LEFT(@bootleg,CHARINDEX('"',@bootleg,0)-1)
SELECT RIGHT(@bootleg,CHARINDEX('"',REVERSE(@bootleg),0)-1)

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources