CODEWITHSUNDEEP
sqloracle-database
Alina Anjum
Alina Anjum

Reputation: 1230

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

Upvotes: 2

Views: 5447

Answers (3)

Rahul Khandelwal Systematix
Rahul Khandelwal Systematix

Reputation: 141

Try to use this query, I am sure it will get the result as you are expecting -

I have used the table name - numbers Column names - id, phone

SELECT id,
  CASE WHEN phone REGEXP ',' THEN replace(substr(phone, 1, instr(phone, ',') -1),'-','') ELSE replace(substr(phone, 1, phone),'-','') END
FROM numbers UNION SELECT id, replace(substr(phone, 1, phone),'-','') FROM numbers WHERE phone NOT REGEXP ',' ORDER BY id

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

This trick would make your query work: mobile_tel || ','

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

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Upvotes: 4

Related Questions