Selecting Substrings from Column

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)

Selecting Substrings from Column

Author

Topic

Glen_D
Starting Member

4 Posts

Posted - 2008-08-02 : 20:57:34

Hi...

I have this data in one column:

N230/231
N237, N258, N227, N231, N208, N209
4800

I need to break this out as separate fields for each number (there 2000+ rows of info like this). I started working on a case stmt below:

SELECT Building_Nos,
(select case when charindex(',', building_nos) > 0 then substring(building_nos, 1, (charindex(',', building_nos)-1))
else
substring(building_nos, 1, (charindex(' ', building_nos)-1))
FROM tbl_Facility
end

But the code has a syntax issue and I need to extract all 6 items in line 2 separately plus all the info from the other rows.

Thanks,

Glen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-02 : 21:02:35

Use a CSV splitter function. There are a number of them here, such as CSVSplit or fnParseList. Just search for them and you should be able to solve your issue. Let us know if you need additional help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Glen_D
Starting Member

4 Posts

Posted - 2008-08-03 : 04:04:20

Thanks but I have treied a few different pieces of code and I'm almost there but not quite. My table name is tbl_Facility and the field I need to 'split' = BuilingNos.

Can you provide some code just to get me started?

Thx,

Glen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-03 : 13:25:40

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104485

Glen_D
Starting Member

4 Posts

Posted - 2008-08-04 : 07:40:51

OK...that works great for all interger data but I have a combination of text and interger data, i.e. Facility A, 1234, XY12...

any ideas to extract all to include text?

Thanks Again,

Glen

Glen_D
Starting Member

4 Posts

Posted - 2008-08-05 : 13:36:10

OK...made this work:

ALTER FUNCTION [dbo].[ParseValues]
(@String varchar(8000)
)
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val varchar (MAX)
)
AS
BEGIN
DECLARE @Value varchar(1000)

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-05 : 13:43:47

quote:
Originally posted by Glen_D

OK...made this work:

ALTER FUNCTION [dbo].[ParseValues]
(@String varchar(8000)
)
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val varchar (MAX)
)
AS
BEGIN
DECLARE @Value varchar(1000)

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

Cool

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources