SQL - Extract Substring

user_18188312 0
Viewed 41

Not great with SQL and am kind of stuck as to which expression to use. I was thinking a "case when" could help me here.

So I have a messy dataset that contains a mixture of information. I am trying to write a SQL statement that would extract only the rows which contain text in the following format: "ID [Int][Int][Int][Int][Int]" or "ID[Int][Int][Int][Int][Int]".

ID # is always 5 integers [0-9]. Sometimes there is no space between ID and the start of the integer.

The logic is essentially this:

  • If (Name) contains "ID 12345" then return "ID 12345" in a separate column.
  • If (Name) contains "ID54321" then return "ID 54321" in a separate column.

Example:

Name Normalized Name
John Smith ID 12345 ID 12345
Hello ID54321 Hello ID 54321

Any assistance you could provide would be greatly appreciated. Thank you!

sql
1 Answers

You can use regexp for it like this:

SELECT *
FROM tablename
WHERE REGEXP_LIKE (Name, '(.)+ID( )+[0-9]+(.)+');
user_62856000
Related