4 Replies Latest reply on Feb 15, 2013 3:18 PM by Programmer Analyst

# Substring

Hello Gurus,
I have a string which contains about 8 values that I need to break them up into individual pieces.
the string looks somewhat like this..

9.7^(Underweight);4 ft 4 in^(02/03/2004);123 lb^(01/02/2004);231/23^(04/05/2005)

• ###### 2. Re: Substring
I see two different cases to handle 1) using ; and 2) using ^

Identify the position of ; and substring to that poistion, for the same query would be like

SELECT
SUBSTRING('9.7^(Underweight);4 ft 4 in^(02/03/2004);123 lb^(01/02/2004);231/23^(04/05/2005)',1,
CHARINDEX(';','9.7^(Underweight);4 ft 4 in^(02/03/2004);123 lb^(01/02/2004);231/23^(04/05/2005)'))
---> This would return value 9.7^(Underweight); ---calling as-->A

Then use the output ie. 9.7^(Underweight); to replace with '' to actual string for the same query would be like

SELECT
REPLACE('9.7^(Underweight);4 ft 4 in^(02/03/2004);123 lb^(01/02/2004);231/23^(04/05/2005)',
SUBSTRING('9.7^(Underweight);4 ft 4 in^(02/03/2004);123 lb^(01/02/2004);231/23^(04/05/2005)',1,
CHARINDEX(';','9.7^(Underweight);4 ft 4 in^(02/03/2004);123 lb^(01/02/2004);231/23^(04/05/2005)')),'')
---> This would return value 4 ft 4 in^(02/03/2004);123 lb^(01/02/2004);231/23^(04/05/2005)

Identify the position of ; again from above output you get value as
4 ft 4 in^(02/03/2004);----calling as--->B

Do the same to get C as
123 lb^(01/02/2004);

D as 231/23^(04/05/2005)==>for this logic might be different

Now its turn for split based on ^

If helps mark and no more questions pls for the same :)

I've Used SQL server db.
• ###### 3. Re: Substring
Thanks for the response Srini.

I forgot to mention, I am trying to split this in BMM. I know I can still try using substring in similar way but just letting you know so you can provide easier ideas if there are any with regard to this.
Meanwhile, I will try to implement your suggestion.
• ###### 4. Re: Substring
Works great...thanks Srini