Alina Anjum Alina Anjum - 1 year ago 87
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 :


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 :

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 Source

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,'-','')
FROM tbl

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

select mobile_tel, 
from tbl
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download