Extracting numerics from a astring

[Liam1]

Hello,

I would like to take a text string, find a numeric, and substring it from the numerical value - 1
e.g.

'BS12 6QQ' I would like to turn into 'BS'

I also need to turn the above into
'BS12'
with the proviso you could potentially have BS1 6QQ, and I would want to return 'BS1'.

so far I have got
Case When Substr(PstCd,2,1) In ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
Then Substr(PstCdOutb,1,1)
Else Substr(PstCdOutb,1,2)
End As PostArea.

This only adresses the 1st part of the question, and not the second.

Is there something snazzy you can do, or is a multiple Case statement the only way?

Thanks in advance,

Liam1.

 

[BillDHS]

Use the INDEX function with substring:
SUBSTR(PstCd, 1, (INDEX(PstCd, ' ') -1)).

You can also look at using this locate the first numeric value, but it won't be as clean.