Extracting a part of data field

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 2000 Forums

Transact-SQL (2000)

Extracting a part of data field

Author

Topic

IKU
Starting Member

11 Posts

Posted - 2009-03-26 : 08:35:22

I need to extract a part of data field which is a part of a column in a table.
Data looks like this:
12345-678; 123456-678; 12345-6009; 123456-6009

I need a report grouped by digits which are behind the '-' regardless their length or position (i cannot use substring, because first part of the column has variable length)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-26 : 08:44:17

Not sure on the desired output since you didn't post one.
May be this ?

Create FUNCTION ParseValues  
(@String varchar(8000)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val varchar(100) 
)  
AS  
BEGIN  
DECLARE @Value varchar(100)  
  
WHILE @String is not null  
BEGIN  
SELECT @Value=CASE WHEN CHARINDEX(';',@String) >0 THEN LEFT(@String,CHARINDEX(';',@String)-1) ELSE @String END,  
@String=CASE WHEN CHARINDEX(';',@String) >0 THEN SUBSTRING(@String,CHARINDEX(';',@String)+1,LEN(@String)) ELSE NULL END  
INSERT INTO @RESULTS (Val)   
SELECT @Value   
END  
RETURN  
END

select 
	distinct
	ltrim(left(val,charindex('-',val)-1) )
from 
	ParseValues( '12345-678; 123456-678; 12345-6009; 123456-6009')

IKU
Starting Member

11 Posts

Posted - 2009-03-26 : 08:59:26

The desired output would be for example if i used count
678; 2
6009; 2
.
.
.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-26 : 09:21:04

replace the second part of what I provided before to this then,

select 
	
	substring(val,charindex('-',val)+1,len(val)),count(*)
from 
	ParseValues( '12345-678; 123456-678; 12345-6009; 123456-6009')
group by 
	substring(val,charindex('-',val)+1,len(val))

IKU
Starting Member

11 Posts

Posted - 2009-03-26 : 09:22:23

Thank you, your solution works, I just changed it a bit so that it matches my needs

substring(val, charindex('-',val)+1, datalength(rtrim(val)))

it was all about charindex, so I didn't have to create function

IKU
Starting Member

11 Posts

Posted - 2009-03-26 : 09:24:50

Ignore my last post, I wrote it while you were writing yours :)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-26 : 09:26:44

great !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources