How to remove Roman letter and numeric value from column in SQL

Question

Using SQL Server, I have a column with numeric and Roman numerals at the end. How do I remove the numeric alone without specifying the position?

Job_Title
Data Analyst 2
Manager 50
Robotics 1615
Software Engineer
DATA ENGINEER III

I tried using this query:

SELECT 
    CASE 
        WHEN PATINDEX('%[0-9 ]%', job_title) > 0 
            THEN RTRIM(SUBSTRING(Job_title, 1, PATINDEX('%[0-9 ]%', job_title) - 1))
            ELSE JOB_TITLE
    END
FROM
    my_table
WHERE 
    PATINDEX('%[0-9]%', JOB_TITLE) <> 0

But the result I'm getting is:

Job_Title
Data
Manager
Robotics

Can you also add the value `Who Am I` to the sample data? – jarlh Jan 27 '22 at 16:23 — jarlh, Jan 27 '22 at 16:23

score 0 · Answer 1 · answered Jan 27 '22 at 16:19

You should remove the space character in the regex expression. So, new code should be

    SELECT case when patindex('%[0-9]%', job_title) > 0 then
    rtrim(substring(Job_title,1, patindex('%[0-9]%', job_title) - 1))
  else
    JOB_TITLE
  end 
from my_table
WHERE PATINDEX('%[0-9]%',JOB_TITLE) <>0

score 0 · Accepted Answer · answered Jan 27 '22 at 16:20

0

Use the TRANSLATE function like this :

SELECT TRANSLATE(Job_title, '0123456789', '          ') AS JOB_TITLE
from my_table

You can use RTRIM to complete

answered Jan 27 '22 at 16:20

SQLpro

3,994
1
6
14

Wow That was quiet very easy one ,now I just use single statement to update the column .which is brilliant thank you so much . – lava_kkk Jan 27 '22 at 18:55

score 0 · Answer 3 · answered Jan 27 '22 at 16:34

I think you're trying to remove numbers from the end of a job title, and not exclude results. So, as others have mentioned, you need to remove the space from the brackets of the regex and put it in front of the brackets to say it is separated from the stuff in front of it by a space. But I think you also need to remove the wildcard character from the right side of the comparison value so that the numbers have to be at the end of the job title, like...

SELECT case when patindex('% [0-9]', job_title) > 0 then
    rtrim(substring(Job_title,1, patindex('% [0-9]', job_title) - 1))
  else
    JOB_TITLE
  end 
from my_table

But, you also mention roman numerals... and... that's tougher if it's possible for a job title to end in something like " X" where it means "X" and not "10". If that's not possible, you should just be able to do [0-9IVXivx] to replace all the bracketed segments.

I already tried removing spaces in patindex '% [0-9]', but nothing happens i got original data with numbers at end. yeah roman numerals are quite a catch trying my best. I used the translate function which actually works by removing numbers . Thank you for your help . — lava_kkk, Jan 27 '22 at 18:51

How to remove Roman letter and numeric value from column in SQL

3 Answers3