Extract Text - 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)

Extract Text

Author

Topic

pradeep_iete
Yak Posting Veteran

84 Posts

Posted - 2008-07-18 : 03:49:33

If particular row of table has the records as

Australia/Nzealand/Uk/Singapore/Other

I want to extract like;
Expected Result set :
australia
Newzealand
UK
Singapore
other

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-18 : 04:28:07

Have a look at the fnParseList function here at SQLTeam.

E 12°55'05.25"
N 56°04'39.16"

ranganath
Posting Yak Master

209 Posts

Posted - 2008-07-18 : 04:57:21

Hi

try with this

DECLARE @data NVARCHAR(MAX),
@delimiter NVARCHAR(5)
SELECT @data = 'Australia/Nzealand/Uk/Singapore/Other',
@delimiter = '/'
DECLARE @textXML XML;
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);
--SELECT @textXML
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 05:17:45

[code]CREATE FUNCTION ParseValues
(@String varchar(8000),
@Delimiter char(1)
)
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val varchar(1000)
)
AS
BEGIN
DECLARE @Value varchar(100)

WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN LEFT(@String,CHARINDEX(@Delimiter,@String)-1) ELSE @String END,
@String=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+1,LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END
GO

SELECT * FROM ParseValues('Australia/Nzealand/Uk/Singapore/Other','/')[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources