May 19, 2010 at 1:43 pm
I have 2 separate tables 1 normalized and the other not. It does however have 1 column that contains the data I need to join to the normalized table. The column is 1 long string concatenated with a '~'. I am trying to split the string into parts but am having trouble figuring this one out. The first part is a fixed length but the second part is not a fixed length.
I can get the first part (in bold) of the string but not sure how to grab the second part (bold).
This is how I'm doing that:
left(substring(docid,charindex('~',docid)+1,len(docid)),7)
Table snippet:
99~B007340~9822151~LR5~250
99~B007599~9417974~LC0~240
Thanks for taking a look.
May 19, 2010 at 2:06 pm
I finally hit my search correctly... Jeff Moden already posted an amazingly simple solution!
http://www.sqlservercentral.com/Forums/FindPost452711.aspx
Thread closed....
May 19, 2010 at 2:07 pm
Are the format of the records in this table all the same? try these
declare @vt_t table (docid varchar(50))
insert into @vt_t
select '99~B007340~9822151~LR5~250' union all
select '99~B007599~9417974~LC0~240'
select substring(docid,charindex('~',docid)+1,7)FirstString,
substring(docid,CharIndex('~', docid, charindex('~',docid)+1 + 1) + 1, 7) SecondString
from @vt_t
-- Use this if the format for all records are the same
select substring(docid, 4, 7) FirstString,
substring(docid, 12,7) SecondString
from @vt_t
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2010 at 2:11 pm
Mike.. the parts are not fixed length.
Your first select is returning great results but the occasional '~' is on the end of some records.
B005739997706~
I added a replace on the SecondString.
select substring(docid,charindex('~',docid)+1,7)FirstString,
replace(substring(docid,CharIndex('~', docid, charindex('~',docid)+1 + 1) + 1, 7),'~','') SecondString
from mytable
May 19, 2010 at 2:17 pm
Mike-I just realized the query is substring the first 7 characters of the SecondString. That string will be variable in length.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply