JBone JBone - 1 month ago 7
SQL Question

Extract a number from String in SQL

I have the following string:

"FLEETWOOD DESIGNS 535353110XXXXX" (The X's are actually numbers I just wanted to hide them here)

Does anyone know how can I search through Strings in SQL and extract numbers that are greater then lets say 10 characters long?

ain ain
Answer

You don't mention the DB engine, so we don't know what features are available...

If regexpressions are available then pattern like \d{10,} would match numbers with 10 or more digit.

In mySQL REGEXP can only return true or false (0 or 1) so you'd have to use some ugly hack like

SELECT
  LEAST(
     INSTR(field,'0'),
     INSTR(field,'1'),
     INSTR(field,'2'),
     INSTR(field,'3'),
     INSTR(field,'4'),
     INSTR(field,'5'),
     INSTR(field,'6'),
     INSTR(field,'7'),
     INSTR(field,'8'),
     INSTR(field,'9')
  ) AS startPos,
  REVERSE(field) AS backward,
  LEAST(
     INSTR(backward,'0'),
     INSTR(backward,'1'),
     INSTR(backward,'2'),
     INSTR(backward,'3'),
     INSTR(backward,'4'),
     INSTR(backward,'5'),
     INSTR(backward,'6'),
     INSTR(backward,'7'),
     INSTR(backward,'8'),
     INSTR(backward,'9')
  ) AS endPos,
  SUBSTRING(field, startPos, endPos - startPos + 1)
FROM tab
WHERE(field REGEXP '[0-9]{10,}')

but this isn't perfect - it would extract false substring for string like "ABC 9 A 1234567891", not to mention that it is probably so slooooow that it is faster to go througt data by hand.