Search code examples
sqlsql-serversubstringpatindex

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

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
Solution

  • Use the TRANSLATE function like this :

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

    You can use RTRIM to complete