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.
| Author |
Topic |
|
hdv212
Posting Yak Master
140 Posts |
 Posted - 2011-01-31 : 02:46:29
|
| hii want to split an string into sub strings as follow :here is my original string : xxxx/xx/xxxsplit by '/'and get 3 sub strings : xxxx and xx xxcan anybody help me ?thanks in advance |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
Ranjit.ileni
Posting Yak Master
183 Posts |
Posted - 2011-01-31 : 03:49:45
|
| Hi,CREATE FUNCTION dbo.Split ( @Delimiter varchar(5), @List varchar(8000) ) RETURNS @TableOfValues table ( RowID smallint IDENTITY(1,1), [Value] varchar(50) ) AS BEGIN DECLARE @LenString int WHILE len( @List ) > 0 BEGIN SELECT @LenString = (CASE charindex( @Delimiter, @List ) WHEN 0 THEN len( @List ) ELSE ( charindex( @Delimiter, @List ) -1 ) END ) INSERT INTO @TableOfValues SELECT substring( @List, 1, @LenString ) SELECT @List = (CASE ( len( @List ) - @LenString ) WHEN 0 THEN '' ELSE right( @List, len( @List ) - @LenString - 1 ) END ) END RETURN END --String Containing Numeric values. SELECT Value FROM dbo.Split( ',', '11,23,3,14' ) output:Value1123314--String Containing Character values.SELECT Value FROM dbo.Split( '|', 'Bob|Jane|Mary|Li|Hsiao|Lubor' ) AS soutput:ValueBobJaneMaryLiHsiaoLubor IRK |
 |
|
|
Ranjit.ileni
Posting Yak Master
183 Posts |
Posted - 2011-01-31 : 04:10:04
|
| YOUR original string : xxxx/xx/xxxoutput: XXXX xx XXXSELECT SUBSTRING((SELECT ( ' ' + Value )FROM dbo.Split( '|', 'XXXX|xx|XXX' ) AS s FOR XML PATH( '' )), 2,1000 )IRK |
|
|
|
|
|
|
|
|
how to split string by an special character ?