Separate track to several fields

Question

Separate track to several fields

SysEdw

SSC Veteran

Points: 270
More actions
January 10, 2011 at 8:17 am

#243453

Hi good day to all
My problem is that I fill a table from another table data in the source table I have the name field but is complete (name, last name, mother's maiden name), and the target will be divided by a column name, another column last name and mother's maiden name another field, so I have to divide the first column of the table.
I'm working with sql server 2008.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

it depends on your data.

if you are absolutely sure there are only two spaces in the field for every single record, you could use something like CHARINDEX2 and substrings to chop it into 3 fields: here;a an example where the data is split with CrLF, but the idea is the same:

/*

Example:

SELECT dbo.CHARINDEX2('a', 'abbabba', 3)

returns the location of the third occurrence of 'a'

which is 7

*/

CREATE FUNCTION CHARINDEX2(

@TargetStr varchar(8000),

@SearchedStr varchar(8000),

@Occurrence int)

RETURNS int

AS

BEGIN

DECLARE @pos int, @counter int, @ret int

SET @pos = CHARINDEX(@TargetStr, @SearchedStr)

SET @counter = 1

IF @Occurrence = 1

SET @ret = @pos

ELSE

BEGIN

WHILE (@counter < @Occurrence)

BEGIN

SELECT @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1)

SET @counter = @counter + 1

SET @pos = @ret

END

RETURN(@ret)

END

GO

#T=CHARINDEX2 usage example

Create Table #Example(LongAddress varchar(500) )

INSERT INTO #Example

SELECT 'RIGDON MILLER & CO REAL ESTATE' + CHAR(13) + CHAR(10) + '1936 SPRUCE ST' + CHAR(13) + CHAR(10) + '19103' UNION ALL

SELECT 'BRUNSON, MIRIAM' + CHAR(13) + CHAR(10) + '7238 SANSOM ST' + CHAR(13) + CHAR(10) + '19082' UNION ALL

SELECT 'SMITH, JAMES J' + CHAR(13) + CHAR(10) + '1600 JACKSON ST' + CHAR(13) + CHAR(10) + '19145' UNION ALL

SELECT 'MERTLE, JESSICA' + CHAR(13) + CHAR(10) + '205-15 ROCK ST' + CHAR(13) + CHAR(10) + '19128' UNION ALL

SELECT 'JOHNSON, KALI' + CHAR(13) + CHAR(10) + '9815 HALDENMAN AVE' + CHAR(13) + CHAR(10) + '19115' UNION ALL

SELECT 'PERSAND, GRACE' + CHAR(13) + CHAR(10) + '333 W ABBOTSFORD RD' + CHAR(13) + CHAR(10) + '19144' UNION ALL

SELECT 'LAW, ANTHONY' + CHAR(13) + CHAR(10) + '333 W ABBOTSFORD RD' + CHAR(13) + CHAR(10) + '19144'

SELECT

SUBSTRING(LongAddress,1, dbo.CHARINDEX2(CHAR(13) + CHAR(10),LongAddress,1)),

SUBSTRING(LongAddress,dbo.CHARINDEX2(CHAR(13) + CHAR(10),LongAddress,1), dbo.CHARINDEX2(CHAR(13) + CHAR(10),LongAddress,2)),

SUBSTRING(LongAddress, dbo.CHARINDEX2(CHAR(13) + CHAR(10),LongAddress,2),30)

FROM #Example

--only the items with 3 parts/2CrLf's

WHERE dbo.CHARINDEX2(CHAR(13) + CHAR(10),LongAddress,2) > 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 736101 More actions · Answer 2

As Lowell mentioned, you need to parse out the field, but this can only be done with patterns.

So you will have to setup multiple parses to handle different cases and use that to move the data. You will want to also make sure your destination table includes the PK from the primary table so you can track what is moved in case you need to handle separate cases with a few passes through the data