Search code examples
node.jsfirebirdphone-number

Firebird query - numbers only

I'm doing a Firebird SQL query in which the column_type is VARCHAR and it's about phone numbers. The query is executing in my nodeJS app. The database has results like:

  • 00-234-567
  • 12/239-344
  • +388-13123/323
  • etc.

But I want the result in the format which contains numbers only. So only the

123321232

format can be accepted. I want the result to be formatted by the query, not in the node js. I've readed some topics about rtrim but it's replace by trim now which wouldn't allow this? Casting as a numeric type would be probably an ultimate fail. What would be a valid solution here?

EDIT: I've found some informations, but I'm still not sure if that would be valid. Query: 

CASE 
  WHEN S.TELEFON SIMILAR TO '[0-9]+' THEN CAST(S.TELEFON AS INTEGER) 
  ELSE ''
  END
    AS PHONE
Solution

  • I guess you could use the REPLACE function to replace all the non-numbers with empty string:

    select
  replace(
    replace(
      replace(S.TELEFON, ' ', ''), -- space
    '-', ''),
  '/', '') AS phone
from mytab S

    It's ugly and only works when you have limited number of different characters you need to get rid off (and which you know beforehand).