Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

parse/split string Expand / Collapse
Author
Message
Posted Wednesday, May 19, 2010 1:43 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 24, 2013 3:29 PM
Points: 67, Visits: 236
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.
Post #924624
Posted Wednesday, May 19, 2010 2:06 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 24, 2013 3:29 PM
Points: 67, Visits: 236
I finally hit my search correctly... Jeff Moden already posted an amazingly simple solution!

http://www.sqlservercentral.com/Forums/FindPost452711.aspx


Thread closed....
Post #924643
Posted Wednesday, May 19, 2010 2:07 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:08 AM
Points: 1,221, Visits: 1,280
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/
Post #924646
Posted Wednesday, May 19, 2010 2:11 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 24, 2013 3:29 PM
Points: 67, Visits: 236
Mike.. the parts are not fixed length.
Your first select is returning great results but the occasional '~' is on the end of some records.

B005739 997706~

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
Post #924650
Posted Wednesday, May 19, 2010 2:17 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 24, 2013 3:29 PM
Points: 67, Visits: 236
Mike-I just realized the query is substring the first 7 characters of the SecondString. That string will be variable in length.
Post #924656
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse