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

David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
MR_E
  • 13
  • 4
  • 7
    Tag your question with the database you are using. – Gordon Linoff Sep 05 '17 at 12:06
  • thought I did - apologies, updated – MR_E Sep 05 '17 at 13:06
  • Add a data sample (5-10 rows) and the requested result, both in a table form. – David דודו Markovitz Sep 05 '17 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](https://stackoverflow.com/questions/8928378/using-regex-in-sql-server), [MySQL](https://stackoverflow.com/questions/6067343/how-to-use-regex-in-mysql). Not sure if you're using a different database as you haven't specified. – ctwheels Sep 05 '17 at 13:31
  • Is it always 10 digits? Is there a space missing in your 3rd example? – David דודו Markovitz Sep 06 '17 at 06:29
  • Hi Dudu, yes as per the example it could have a space missing, just to add complexity to the subject. – MR_E Sep 06 '17 at 12:08

0 Answers0