Search code examples
sqlreplacenumeric

How to Extract only numbers from the String without using function in SQL

Table contains data as below

Table Name is REGISTER

Column Name is EXAM_CODE

Values like ('S6TJ','S7','S26','S24')

I want answer like below

Result set - > (6,7,26,24)

Please suggest solution - since regexp_replace is not recognized built in function name in SQL.

Solution

  • The complexity of the answer depends on two things: the RDBMS used and whether the numbers in the EXAM_CODE are contiguous.

    I have assumed that the RDBMS is SQL Server and the numbers in EXAM_CODE are always contiguous. If not, please advise and I can revise the answer.

    The following SQL shows a way of accomplishing the above using PATINDEX.:

    CREATE TABLE #REGISTER (EXAM_CODE VARCHAR(10));
INSERT INTO #REGISTER VALUES ('S6TJ'),('S7'),('S26'),('S24');

SELECT  LEFT(EXAM_CODE, PATINDEX('%[^0-9]%', EXAM_CODE) - 1)
  FROM  (
        SELECT  RIGHT(EXAM_CODE, LEN(EXAM_CODE) - PATINDEX('%[0-9]%', EXAM_CODE) + 1) + 'A' AS EXAM_CODE
          FROM  #REGISTER
        ) a

DROP TABLE #REGISTER

    This outputs:

    6
7
26
24

    PATINDEX matches a specified pattern against a string (or returns 0 if there is no match).

    Using this, the inner query fetches all of the string AFTER the first occurence of a number. The outer query then strips any text that may appear on the end of the string.

    Note: The character A is appended to the result of the inner query in order to ensure that the PATINDEX check in the outer query will make a match. Otherwise, PATINDEX would return 0 and an error would occur.