Data extraction from a concatenated field

Question

Post reply

Data extraction from a concatenated field

DerbyNeal

Hall of Fame

Points: 3854
More actions
June 16, 2008 at 9:50 am

#381140

Hi,
I'm using SQL 2005 T-SQL.
I'm need to extract partial data from a concatenated address field.
The address field contains 5 elements, each separated by '/n'.
In this particular example, I need to retrieve the 3rd element 'Morley'.
Acme Products/n1551 Main Street/nMorley/nEast Place/nStaffs
Can anyone help?
Thanks in advance,
Neal

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 1

Use charindex/patindex to find the place where the occurs and then SUBSTRING to extract around it.

noeld SSC Guru Points: 96590 More actions · Answer 2

nealwright44 (6/16/2008)
Hi,
I'm using SQL 2005 T-SQL.
I'm need to extract partial data from a concatenated address field.
The address field contains 5 elements, each separated by '/n'.
In this particular example, I need to retrieve the 3rd element 'Morley'.
Acme Products/n1551 Main Street/nMorley/nEast Place/nStaffs
Can anyone help?
Thanks in advance,
Neal

declare @t table ( addr varchar(200))

insert into @t (addr ) values ( 'Acme Products/n1551 Main Street/nMorley/nEast Place/nStaffs')

select q.id as field_value

from

(select replace(addr, '/n','~') as ad from @t ) w

outer apply

(

select row_number() over(order by n) as rn, id

from

(

select n, substring('~'+ ad + '~', n + 1, charindex('~', '~'+ ad + '~', n + 1 ) -n -1) as id

from (select number as n from master..spt_values where type = 'P' ) tally

where n <= len( '~' + ad + '~') -1

and substring( '~' + ad + '~', n, 1) = '~'

) h

)q

where q.rn = 3 ---- choose your field number ( 1 based)

* Noel

DerbyNeal Hall of Fame Points: 3854 More actions · Answer 3

Thanks guys,

I created this function, based on CHARINDEX ... (I didn't mention previously, but the the field I wish to output to has a field size of 30. Hence the @strOut declaration below).

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[ExtractPartialData]

(

@strIn VARCHAR(250)

)

RETURNS VARCHAR(250)

AS

BEGIN

IF @strIn IS NULL

RETURN NULL

DECLARE

@strOut VARCHAR(30)

SELECT

--Substring used to extract data between the 2nd occurence of '/n' and the 3rd occurrence of '/n'

@strOut = SUBSTRING(@strIn,

CHARINDEX('/n', @strIn, CHARINDEX('/n', @strIn)+1) + 2,

CHARINDEX('/n', @strIn, CHARINDEX('/n', @strIn, CHARINDEX('/n', @strIn)+1)+1) - CHARINDEX('/n', @strIn, CHARINDEX('/n', @strIn)+1)-2)

--END

RETURN @strOut

END

Neal

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 4

Please see the following for how to do a numbered "split"

http://www.sqlservercentral.com/articles/T-SQL/63003/...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)