seperating 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)

seperating string

Author

Topic

goodsolution
Starting Member

38 Posts

Posted - 2009-05-08 : 15:15:37

select 'GAS14 GSU02 GSU10 INF06'

I want to seperate that record into 4 records like as shown below

col
GAS14
GSU02
GSU10
INF06

Note : Each record is seperating with "Space"

-Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-08 : 15:24:41

declare @var char(25)
set @var = 'GAS14 GSU02 GSU10 INF06'

select parsename(replace(@var,' ','.'),4)
,parsename(replace(@var,' ','.'),3)
,parsename(replace(@var,' ','.'),2)
,parsename(replace(@var,' ','.'),1)

but you can search this forum for better parsing algorithms

Jim

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-08 : 15:30:17

Use a split function, this post shows a few differant ones.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=3

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

goodsolution
Starting Member

38 Posts

Posted - 2009-05-08 : 15:55:03

Jim,
It is working fine for 4 records,
i am having the biggest is
'CAR01 CAR04 EAR12 GAS01 GAS08 GTC01 GUR02 NEW04 NUR19 RES01 RES05 RES11'
for this it is giving me null values

-Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-08 : 23:06:34

using fnParseList from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

select *
from fnParseList(' ', 'CAR01 CAR04 EAR12 GAS01 GAS08 GTC01 GUR02 NEW04 NUR19 RES01 RES05 RES11')

KH
[spoiler]Time is always against us[/spoiler]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-08 : 23:13:34

Here is a function taken directly from the link I posted.


CREATE FUNCTION dbo.Split
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1

	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
END


select * from dbo.split('CAR01 CAR04 EAR12 GAS01 GAS08 GTC01 GUR02 NEW04 NUR19 RES01 RES05 RES11',' ')

RESULTS


Id          Data
----------- ----------
1           CAR01
2           CAR04
3           EAR12
4           GAS01
5           GAS08
6           GTC01
7           GUR02
8           NEW04
9           NUR19
10          RES01
11          RES05
12          RES11

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-05-09 : 02:54:48

Hi Try this once,

Declare @Str1 VARCHAR(100)
Select @Str1 = 'GAS14 GSU02 GSU10 INF06'

;WITH csvtbl(i, j)
AS
(
SELECT 0, j = CHARINDEX(' ', @Str1+' ')
UNION ALL
SELECT CAST(j + 1 AS INT), j = CHARINDEX(' ', @Str1+' ', j + 1)
FROM csvtbl
WHERE CHARINDEX(' ', @Str1+' ', j + 1) <> 0
)
SELECT SUBSTRING(@Str1, C.i, C.j-i) as 'Column' from csvtbl c

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources