String manipulation

Question

String manipulation

vbradham

SSC Enthusiast

Points: 197
More actions
July 2, 2010 at 5:00 am

#226406

In table A, Col1
I have a string in a column delimited by pipes.
|xxxxx|xxxxx| | |xxxxx|
In table B, Col1, Col2
I have two columns. Column1 needs to go into the first empty column, and Column2 needs to go into the second empty column.
I am new to TSQL - still learning, please advise.

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

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

I have a suspicion, that the data in the table A are loading from some where. Id it is true, than the split should happen during the load other then in T-SQL. You can use bcp or SSIS to do so easely.

However if you really desperate to have it done in T-SQL, and the format you gave is fixed and data is clear (no empty values, all bars are present etc.) you can use the follwoing "noodles":

insert into B (Col1, Col2)

select LEFT( SUBSTRING(Col1,2,LEN(Col1)) ,CHARINDEX('|',SUBSTRING(Col1,2,LEN(Col1)),1) -1) as Col1

,LEFT( SUBSTRING(SUBSTRING(Col1,2,LEN(Col1)),CHARINDEX('|',SUBSTRING(Col1,2,LEN(Col1)),1)+1,

LEN(Col1)), CHARINDEX('|',SUBSTRING(SUBSTRING(Col1,2,LEN(Col1)),

CHARINDEX('|',SUBSTRING(Col1,2,LEN(Col1)),1)+1, LEN(Col1)), 1)-1) as Col2

from A

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

vbradham SSC Enthusiast Points: 197 More actions · Answer 2

Yes true - table A is a table to hold the values to update the string with; joined on unique id.

Using sql 2000 so has to be T-sql. Thanks will try it.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

vbradham (7/2/2010)
...
Using sql 2000 so has to be T-sql. Thanks will try it.
...

How you get this bar separated data in a first place?

Does it come from GUI or loaded from somewhere.

BTW.

bcp exists since before SQL Server itself 😀

SSIS is "a child" of DTS which you can also use...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

This question is a common one. You can find a lot of different approaches if you will search this site using the keywords "PARSE" and "DELIMITED".

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills