CODEWITHSUNDEEP
sqlextractfirebird
Simon King
Simon King

Reputation: 195

Firebird SQL: Extract Number

I have a field called "JobOrder ". Examples of information in the field are:

S15195, CE15195, W16292W01, SW16292W01.1

Is there an SQL statement to extract the number? The number will be 5 digits in 99.99% of the time. (If it is easier then lets say it is always 5 digits). The 5 digit sequence may start with 1 or 2

The following Case Statement checking if second character in the string is 1 else third character is 1 works

Case substring(ordernumber FROM 2 FOR 1)
  when '1' then substring(ordernumber FROM 2 for 5)
  when '2' then substring(ordernumber FROM 2 for 5)
  else substring(ordernumber from 3 for 5)
End As PROJECTNUMBER

Does anyone know of a better way?

Upvotes: 2

Views: 1144

Answers (1)

Aaron Dietz
Aaron Dietz

Reputation: 10277

Another option is to use SIMILAR TO:

CASE WHEN SUBSTRING(ordernumber FROM 2 FOR 5) SIMILAR TO '[0-9]+'
     THEN SUBSTRING(ordernumber FROM 2 FOR 5)
     ELSE SUBSTRING(ordernumber FROM 3 FOR 5)
END as PROJECTNUMBER

This saves you the trouble of checking for both a 1 or 2 starting digit, and is scalable if more starting digits ever arise.

You can build pretty easily on this to account for 4 or 6 digit numbers too (or whatever the other .01% possibility is).

Upvotes: 1

Related Questions