CODEWITHSUNDEEP
sql-servert-sql
learningtech
learningtech

Reputation: 33673

Error with SELECT CASE with regular expression

I wrote the following query in T-SQL for SQL Server

SELECT 
   CASE
      WHEN ADDR_LINE_1 REGEXP '^[0-9]'
      THEN SUBSTRING(ADDR_LINE_1,1,CHARINDEX(' ',ADDR_LINE_1))
      ELSE NULL
   END AS HOUSE_NUMBER
FROM CUSTOMER

What I want is that if the column ADDR_LINE_1 starts with a number, I want to extract the HOUSE_NUMBER from it. But right now my query gives a parse error. If I replace the word REGEXP with LIKE, the parse error goes away, but I always get NULL for HOUSE_NUMBER. What is the correct syntax for my query?

Upvotes: 0

Views: 2447

Answers (2)

PeteGO
PeteGO

Reputation: 5781

You could use ISNUMERIC and LEFT like this.

SELECT
    CASE WHEN ISNUMERIC(LEFT(ADDR_LINE_1, 1)) = 1 
    THEN SUBSTRING(ADDR_LINE_1, 1, CHARINDEX(' ', ADDR_LINE_1))
    ELSE NULL 
    END AS HOUSE_NUMBER
FROM CUSTOMER

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81930

How about using the LIKE

SELECT 
   CASE
      WHEN ADDR_LINE_1 Like '%[0-9]%'
      THEN SUBSTRING(ADDR_LINE_1,1,CHARINDEX(' ',ADDR_LINE_1))
      ELSE NULL
   END AS HOUSE_NUMBER
FROM CUSTOMER

Upvotes: 1

Related Questions