CR9 - extract wildcard from a blob field 1

[JoyCR9]

This forum is awesome. I've learned so much, and yet have so much left to learn!

My problem of the day is:

I have a table (SERVICE_CALL_EVENT) with field (SERVICE_CALL_EVENT.NOTES). This is where my technicians enter the notes of an action performed on a call. It can be up to 4000 characters.

I want to extract SERVICE_CALL_EVENT.SERVICE_CALL_ID where the SERVICE_CALL_EVENT.NOTES contains the pattern ???-???? anywhere in the potential 4000 character string. ???-???? typically is numeric text eg: 540-3445 (part number).

I want my end result to look like:

SERVICE CALL ID PART NUMBER
5566 540-3445
5567 No Part
5568 540-3446
5568 340-1111
5568 111-7777

A call can have more than one part ordered & I must reflect that. A call can have no parts ordered & I must also reflect that. I likely will group by service call id

I couldn't find a similar thread on this already.

Thanks.

 

[Turkbear]

HI,
More info please:

What database?

What version of it?
( if Oracle it will be different from SqlServer, etc.)
The solution involves using wildcards in the where clause...

 

[lbass]

If there are no other "-"s in the field, you could use:

if instr({SERVICE_CALL_EVENT.NOTES},"-") > 0 then
mid({SERVICE_CALL_EVENT.NOTES},
instr({SERVICE_CALL_EVENT.NOTES},"-")-3, 8) else
"No Part"

But this would extract only one part number per note.

-LB

 

[JoyCR9]

if instr({SERVICE_CALL_EVENT.NOTES},"-") > 0 then
mid({SERVICE_CALL_EVENT.NOTES},
instr({SERVICE_CALL_EVENT.NOTES},"-")-3, 8) else
"No Part"


Does pretty much what I want, but you are correct..only one per note.

SQL on Oracle is what I'm working with. Not sure of version numbers.

 

[JoyCR9]

if instr({SERVICE_CALL_EVENT.NOTES},"-") > 0 then
mid({SERVICE_CALL_EVENT.NOTES},
instr({SERVICE_CALL_EVENT.NOTES},"-")-3, 8) else
"No Part"


Gives me alpha info that I don't want. EG: 3rd-repl

I can live with one part per note if I didn't get alpha repsonses, only numeric.

 

[Turkbear]

Hi, in Oracle Sql it would be

where SERVICE_CALL_EVENT.NOTES like '%srchstring%'

Indexing that field may speed things up.

 

[lbass]

The following should work in the case of one result per note:

if instr({SERVICE_CALL_EVENT.NOTES},"-") > 0 and
isnumeric(mid({SERVICE_CALL_EVENT.NOTES},
instr({SERVICE_CALL_EVENT.NOTES},"-")-3, 3)) and
isnumeric(mid({SERVICE_CALL_EVENT.NOTES},
instr({SERVICE_CALL_EVENT.NOTES},"-")+1, 4)
then
mid({SERVICE_CALL_EVENT.NOTES},
instr({SERVICE_CALL_EVENT.NOTES},"-")-3, 8) else
"No Part"

-LB