We help IT Professionals succeed at work.

Split string in SQL Stored procedures

9,990 Views
Last Modified: 2008-05-19
Hi There

I have a string that comes through as follows
A24+B34.56*  or sometimes comes through like this A24 or B34.78

I would like to check the string in my stored procedure.
If there is a + sign in the string I want to split the string into 2 values using the plus sign as the delimiter.
If there is no plus sign I would just like to use the value as it comes.

Thanks
Stanton

Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
something like... ( not tested so you might have to play with the len in the substrings ie -1 or not a bit.
SELECT
   CASE  
        WHEN CHARINDEX( '+',  myField) > 0
                  THEN    
                           SUBSTRING(  myField, 1,  CHARINDEX( '+', myField ) -1 )
        ELSE MYFIELD
  END AS LEFT_FIELD,
  CASE  
        WHEN CHARINDEX( '+',  myField ) > 0
                  THEN    
                             SUBSTRING(  myField, CHARINDEX( '+', myField ) +1 , LEN(myField) -CHARINDEX( '+', myField ) - 1   )
        ELSE
                myField
  END AS RIGHT_FIELD,
FROM myTable
oops still busy typing and angell has already given answer  Sorry
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.