CODEWITHSUNDEEP
sql-serverregex
The6thSense
The6thSense

Reputation: 8335

Regex in sql server to match numeric data

I have a table which looks like 

name 34 
name 4  
name 9  
n1am3e jyhjgyn 797907   
n1am3e 0yhjgyn 797907

Now i want the output to be like 

name    
name    
name    
n1am3e jyhjgyn  
n1am3e 0yhjgyn

That is if the last word is only numeric then remove it

select dad, PATINDEX('% [0-9]%',dad) from (select (name) as dad from name) as c

I tried the above code to get the index but in the last case it gives 7 instead of 15

Upvotes: 0

Views: 82

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93704

Try this .. Use string FunctionsLeft,Reverse and Substring

CREATE TABLE #test(name VARCHAR(50))

INSERT #test
VALUES ('name 34'),('name 4'),('name 9'),
       ('n1am3e jyhjgyn 797907'),
       ('n1am3e 0yhjgyn 797907')

SELECT CASE
         WHEN Patindex('%[a-z]%', LEFT(Reverse(name), CASE
                                                        WHEN Charindex(' ', Reverse(name)) = 0 THEN Len(name)
                                                        ELSE Charindex(' ', Reverse(name))
                                                      END)) = 0 THEN Reverse(Substring(Reverse(name), Charindex(' ', Reverse(name)), Len(name)))
         ELSE name
       END
FROM   #test

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

Try this:

CREATE TABLE temp(
    name VARCHAR(200)
)
INSERT INTO temp VALUES
('name 34'), ('name 4'), ('name 9'), ('n1am3e jyhjgyn 797907'), ('n1am3e 0yhjgyn 7212'), ('n1am3e 0yhjgyn 72e12'), ('11');

SELECT
    CASE
        WHEN CHARINDEX(' ', name) > 1 THEN
            CASE
                WHEN RIGHT(name, CHARINDEX(' ', REVERSE(name)) -1) NOT LIKE '%[^0-9]%' -- check if last word is all digits
                    THEN LEFT(name, LEN(name) - CHARINDEX(' ', REVERSE(name)))
                ELSE name
            END
        ELSE name
    END
FROM temp

DROP TABLE temp

Upvotes: 1

Deepshikha
Deepshikha

Reputation: 10264

You can write a query as:

SELECT 
CASE ISNUMERIC (RIGHT(name, NULLIF(charindex(' ', REVERSE(name)),0)))-- get last word
WHEN 1 THEN -- get string without last word
REVERSE(RIGHT(REVERSE (name), len(name) - NULLIF(charindex(' ', REVERSE(name)),0)))
ELSE name -- get whole string
end
AS dad      
FROM @tbl

Upvotes: 1

Related Questions