0

Is it possible to use a regexp function to identify the numeric value after a certain string of text - within one field?

examples of my values within COL_A are TAX_NO 1234567890, - I would like to match on any values where TAX_NO is present, but I would also like to bring back the number that follows also - the result should be TAX_NO 1234567890.

Which is easy enough as a LIKE '%TAX_NO&', but the problem is that we could have other characters in this field that would read: TAX_NO 1234567890 2 apple street, also occasions where it could be TAX_NO1234567890 2 apple street -

how can I extract just that TAX_NO and the 10 digit number that follows?

Ideally this will be used to match on, using a reference file where TAX_NO 1234567890 is correct and will match against another dataset where this value could be in a number of fields as mentioned with additional text.

Data Sample:

COL_A                                - DESIRED RESULT

TAX_NO 1234567890 MR SMITH           - TAX_NO 1234567890  
1/ TAX_NO 1234567890                 - TAX_NO 1234567890  
TAX_NO1234567890 2 APPLE ST          - TAX_NO 1234567890  
MR SMITH TAX_NO 1234567890           - TAX_NO 1234567890  
SMITH TAX_NO 1234567890 2 APPLE      - TAX_NO 1234567890

tia

Dan

Improve this question
6
  • 7
    Tag your question with the database you are using. Commented Sep 5, 2017 at 12:06
  • thought I did - apologies, updated Commented Sep 5, 2017 at 13:06
  • Add a data sample (5-10 rows) and the requested result, both in a table form. Commented Sep 5, 2017 at 13:17
  • 1
    If you can, you should reorganize the database so that TAX_NO is a different field from its counterpart. That would be the correct solution. Otherwise, take a look at these questions (depending on your SQL database): SQL Server, MySQL. Not sure if you're using a different database as you haven't specified. Commented Sep 5, 2017 at 13:31
  • Is it always 10 digits? Is there a space missing in your 3rd example? Commented Sep 6, 2017 at 6:29
Related questions
0
In Hive I need to Get numeric value after a particular word is it possible?
0
How can I extract the numerical prefix of a string using REGEX_EXTRACT on Hive?
1
Extract field based on any value in between values
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.