How to split the value

Question

Post reply

How to split the value

shuzi

Say Hey Kid

Points: 684
More actions
December 7, 2009 at 3:56 pm

#213286

Currently i have a row with this value
AUDITR 0287 Location 01 Auditor ,Location 16 Auditor,Location 19 Auditor
How can you insert to a table with the value like this?
AUDITR 0287 Location 01 Auditor
AUDITR 0287 Location 16 Auditor
AUDITR 0287 Location 19 Auditor
thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

seth delconte SSCertifiable Points: 6388 More actions · Answer 1

This is probably overly complicated, but I think it works:

Start out with the parse routine:

declare @a varchar(150),

@b-2 varchar(150),

@C varchar(150)

set @a = 'AUDITR 0287 Location 01 Auditor ,Location 16 Auditor,Location 19 Auditor' --plug in your CSV string here

set @b-2 = substring(@a, 0, len(substring(@a, 0, charindex('7', @a, 0)+3))) --delimit the repeated part of the string here and on next line

set @C = substring(@a, charindex('7', @a, 0)+1, len(@a)-len(@b));

with temp_vals as

(

select left(@c, charindex(',',@c) - 1) as val,

stuff(@c +',',1,charindex(',',@c),'') as col

union all

select cast(left(col, charindex(',',col) - 1) as varchar(150)),

stuff(col,1,charindex(',',col),'') from temp_vals

where col <> ''

)

select 'INSERT INTO MY_TABLE VALUES ('''+@b+' '+rtrim(Ltrim(val))+''')' as inserted_text into #insert_sql from temp_vals

That builds SQL INSERT statements for you in a temp table. Then use a cursor (I know it's not always recommended, but it's ok for a limited iteration) to pull out the statements and execute them:

declare @sql varchar(150)

declare _cur cursor for select inserted_text from #insert_sql

open _cur

fetch next from _cur into @sql

while @@fetch_status = 0

begin

select @sql

exec(@sql)

fetch next from _cur into @sql

end

close _cur

deallocate _cur

Now check your newly inserted results:

select * from my_table

I borrowed the parse routine from http://sqlwithtejinderbarnala.blogspot.com/2009/02/how-to-split-words-in-sqlserver.html

_________________________________
seth delconte
http://sqlkeys.com

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

How do you know to split it at:

AUDITR 0287 Location

How would the system know that?

CEWII

arun.sas SSChampion Points: 11831 More actions · Answer 3

Hi,

Cewii lift good question, OP may forget to mentioned this, unless the common character how we move further? Ok let assume and doing,

The common characters exists in the string, try this

declare @result varchar(1000),@common_char varchar(50)

set @common_char = 'AUDITR 0287 '

set @result = 'AUDITR 0287 Location 01 Auditor,Location 16 Auditor,Location 19 Auditor'

set @result = replace (@result,',',''' union select '''+@common_char)

set @result = 'select '''+@result+''''

exec (@result)

RESULT

AUDITR 0287 Location 01 Auditor

AUDITR 0287 Location 16 Auditor

AUDITR 0287 Location 19 Auditor

And we need to put the common characters to the entire row, and then try this

declare @result varchar(1000),@common_char varchar(50)

set @common_char = 'BENIFIT '

set @result = 'AUDITR 0287 Location 01 Auditor,Location 16 Auditor,Location 19 Auditor'

set @result = replace(@result,@common_char,'')

set @result = replace (@common_char+@result,',',''' union select '''+@common_char)

set @result = 'select '''+@result+''''

exec (@result)

RESULT

BENIFIT AUDITR 0287 Location 01 Auditor

BENIFIT Location 16 Auditor

BENIFIT Location 19 Auditor

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

I guess I come back to the fact that for there to be a clean methodology I either need to have a delimiter or a set size to chop off the front. Without that then I have to build extra logic to try and figure out what is wanted. I view this as a poorly thought out method. I would really like the OP to clarify..

CEWII

seth delconte SSCertifiable Points: 6388 More actions · Answer 5

I guess I come back to the fact that for there to be a clean methodology I either need to have a delimiter or a set size to chop off the front. Without that then I have to build extra logic to try and figure out what is wanted. I view this as a poorly thought out method. I would really like the OP to clarify..
CEWII

Good point, I was assuming that the script would have to be modified for each new string that needed to be parsed.

_________________________________
seth delconte
http://sqlkeys.com

shuzi Say Hey Kid Points: 684 More actions · Answer 6

currently this row has three columns, one of the column could or could not have the value with comma delimited value, so i would like to parse this column, then make it insert into another table with the first two columns value, like this with three rows, three column. i could have function created to parse the string and return a table with the value, but don't know how to insert to a new table.

AUDITR 02287 Location 01 Auditor

AUDITR 02287 Location 16 Auditor

AUDITR 02287 Location 19 Auditor

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 7

shuzi,

Did you ever get this sorted out or do you still need some help?

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