Parsing value from string to compare with in sql statement

Brock
Brock used Ask the Experts™
on
Hi,

I want to be able to parse an 'id value' from a description field.  

So values for this id are in bold and underlined for you.  They will always be after the : and before the E of Emplid.

example 1.  REQUEST_ID:RV3-5 EMPLID:2005078 RDC:RA,0,A
example 2.  REQUEST_ID:13 EMPLID:1001716 RDC:RA,0,A

Is there a way you can do this in sql?

Right now the creator of the query is doing this:

AND G.REQUEST_ID = SUBSTRING(B.EOAWDESCR , 13 ,2))

This will not work for the RV3-5 ...

Any help would be greatly appreciated.


Brock
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
I a assuming that "REQUEST_ID:" and " EMPLID:" always exists in the string.
with sample as (
    select 'REQUEST_ID:RV3-5 EMPLID:2005078 RDC:RA,0,A' as description
    union all
    select 'REQUEST_ID:13 EMPLID:1001716 RDC:RA,0,A'
    )

select
*, left(replace(description,'REQUEST_ID:',''), ca.pos) as id
from sample
cross apply (
    select
    charindex(' EMPLID:', replace(description,'REQUEST_ID:','')) pos
    ) ca
    

Open in new window

nb you don't have to use the cross apply it could be done like this:
select
*, left(replace(description,'REQUEST_ID:',''), charindex(' EMPLID:', replace(description,'REQUEST_ID:',''))) as id
from sample

Open in new window

Top Expert 2008

Commented:
You could use

AND G.REQUEST_ID = SUBSTRING(B.EOAWDESCR,CHARINDEX(':',B.EOAWDESCR)+1,CHARINDEX(' EMPLID', B.EOAWDESCR)-CHARINDEX(':',B.EOAWDESCR)-1)

Open in new window

IT / Software Engineering Consultant
Top Expert 2016
Commented:
Just to share a slightly different approach, this assumes:
  • REQUEST_ID: is in the text
  • REQUEST_ID: can be preceded by other text
  • The ID to be extracted is followed by a space
  • EMPLID: doesn't have to be there

select substring(description, charindex('REQUEST_ID:', description)+11, charindex(' ', substring(description, charindex(description, 'REQUEST_ID:')+11, 999))-2) as id

Open in new window



»bp
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi Bill,

I am implementing the solution you have provided -  

I  am trying to also work on the emplid portion  which will also move when the request id expands --


What is the significance of the '-2' ?    I am assuming the 999 - is an approximate max on the string length.  

I would like to reference the numbers as lengths of field values if possible.


Thanks, Brock

Author

Commented:
Sorry,  I just saw the substring on the outside.  Too tired  :-)

Author

Commented:
Okay - I broke down the individual parts and all is clear.

Thank you to all of you for your assistance.

Brock

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial