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
– Jeroen Mostert, Commented Sep 27, 2018 at 8: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. — Gottfried Lesigang
– Gottfried Lesigang, Commented Sep 27, 2018 at 8:31

Gottfried Lesigang · Accepted Answer · 2018-09-27 09:19:15Z

1

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...

edited Sep 27, 2018 at 9:19

answered Sep 27, 2018 at 8:36

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Attie Wagner Over a year ago

Thanks Shnugo, I will try this and revert.

Attie Wagner Over a year ago

Thanks Shnugo, this was exactly what I was looking for - works like a charm!

Attie Wagner Over a year ago

Hi Shnugo, you are correct. Apologies, I will keep that in mind next time.

JonWay · Accepted Answer · 2018-09-28 09:22:53Z

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, 2018 at 9:22

answered Sep 27, 2018 at 8:43

JonWay

1,7471 gold badge21 silver badges48 bronze badges

1 Comment

Attie Wagner Over a year ago

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.

Collectives™ on Stack Overflow

Separating Numeric Values from string

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related