Separating Numeric Values from string

Question

I'm having an issue, whereby I need to separate the following BPO007 to show BPO and 007. In some cases another example would be GFE0035, whereby I still need to split the numeric value from the characters.

When I do the following select isnumeric('BPO007') the result is 0, which is correct, however, I'm not sure how split these from each other.

I've had a look at this link, but it does not really answer my question.

I need the above split for a separate validation purpose in my trigger.

How would I develop something like this?

Thank you in advance.

Hint: `SELECT PATINDEX('%[0-9]%', 'BPO007')`, `LEFT`, `SUBSTRING`. — Jeroen Mostert, Sep 27 '18 at 08:20
Jeroen Mostert has given all the needed tools in his comment. About your question *How would I develop something like this?*: Do not store two pieces of information within the same column (read about *1.NF*). You should keep separate columns in your database for `BPO` and for `007` (or rather `7`) and use some string methods to *compute* the `BPO007` when you need in in your output. — Shnugo, Sep 27 '18 at 08:31

Shnugo · Accepted Answer · 2018-09-27T09:19:15.307

As told in a comment before:

About your question How would I develop something like this?:
Do not store two pieces of information within the same column (read about 1.NF). You should keep separate columns in your database for BPO and for 007 (or rather an integer 7).
Then use some string methods to compute the BPO007 when you need it in your output.

Just not to let you alone in the rain.

DECLARE @tbl TABLE(YourColumn VARCHAR(100));
INSERT INTO @tbl VALUES('BPO007'),('GFE0035');

SELECT YourColumn,pos
      ,LEFT(YourColumn,pos) AS CharPart
      ,CAST(SUBSTRING(YourColumn,pos+1,1000) AS INT) AS NumPart
FROM @tbl 
CROSS APPLY(SELECT PATINDEX('%[0-9]%',YourColumn)-1) AS A(pos);

Will return

YourColumn  pos CharPart    NumPart
BPO007      3   BPO         7
GFE0035     3   GFE         35

Hint: I use a CROSS APPLY here to compute the position of the first numeric character and then use pos in the actual query like you'd use a variable. Otherwise the PATINDEX would have to be repeated...

Thanks Shnugo, this was exactly what I was looking for - works like a charm! — Attie Wagner, Sep 27 '18 at 18:40
Hi Shnugo, you are correct. Apologies, I will keep that in mind next time. — Attie Wagner, Sep 30 '18 at 18:10

JonWay · Answer 2 · 2018-09-28T09:22:53.887

1

Since the number and text varies, you can use the following codes

DECLARE @NUMERIC TABLE (Col VARCHAR(50))
INSERT INTO @NUMERIC VALUES('BPO007'),('GFE0035'),('GFEGVT003509'),('GFEMTS10035')

SELECT
Col,
LEFT(Col,LEN(Col)-LEN(SUBSTRING(Col,PATINDEX('%[0-9]%',Col),DATALENGTH(Col)))) AS TEXTs,
RIGHT(Col,LEN(Col)-LEN(LEFT(Col,LEN(Col)-LEN(SUBSTRING(Col,PATINDEX('%[0-9]%',Col),DATALENGTH(Col)))))) AS NUMERICs

FROM @NUMERIC

edited Sep 28 '18 at 09:22

answered Sep 27 '18 at 08:43

JonWay

1,585
17
37

Thanks JonWay, I don't think it will always by 3 characters as it's based on the user preference, but thanks for your answer. – Attie Wagner Sep 27 '18 at 09:49

Separating Numeric Values from string

2 Answers2