Alina Anjum Alina Anjum - 5 days ago 6
SQL Question

Remove all characters from a string after coma if comma exists

I am getting the mobile number of employee from the table.

The problem is there are one or more numbers are saved in one column separated with comma, and I want to get only first mobile number.

Also mobile number has hyphen

-
sign , I also want to remove it .

For example :

0300-123766, 0312-8789709


I want the output to be :

0300123766


My code is working fine for more than one mobile number but there is a possibility that the column has only one mobile number .

I've tried the following code :

SELECT
mobile_tel,
replace(substr(mobile_tel, 1, instr(mobile_tel, ',') -1), '-', '')
FROM tbl;


but this is only working when string has a comma, otherwise it's returning null.

How can I solve this?

Kindly help

vkp vkp
Answer

You can use a case expression to do this.

SELECT mobile_tel,
       case when instr(mobile_tel, ',') > 0
            then replace(substr(mobile_tel, 1, instr(mobile_tel, ',') -1),'-','')
            else replace(mobile_tel,'-','')
       end
FROM tbl

One more way to get the substring upto the first comma in the string using regexp_substr.

select mobile_tel, 
replace(regexp_substr(mobile_tel,'(^[^,]+),?',1,1,null,1),'-','')
from tbl
Comments