CODEWITHSUNDEEP
sqlsql-servert-sql
M.T Davis
M.T Davis

Reputation: 1098

Replacing empty string in SQL using SELECT

I'm trying to replace empty strings with a value and I can't seem to find the best way to do this.

The issue is that SOME values in the phone_number column are in a format without the numbers. For example ( ) -

I want to replace those empty values with 000-0000. I tried to use the CASE WHEN function but that doesn't seem to address the problem. The COALESCE IFNULL won't work because technically the values aren't NULL just incomplete. I'm thinking perhaps the CASE WHEN function would work if I could figure out how to format the empty values correctly.

Here is an example of the code

SELECT 
    phone_column,
    CASE 
       WHEN phone_column = '() -' 
          THEN '000-000' 
          ELSE SUBSTRING(phone_colum, 6, 8)
    END AS Phone
FROM 
    client_table
ORDER BY 
    linkid_

Upvotes: 0

Views: 2670

Answers (2)

iSR5
iSR5

Reputation: 3498

if you want to search in a varchar then use LIKE this would help you in using expressions. For instance, in your case phone_column = '() -' would be phone_column LIKE '() -%' this will match any string that begins with () -. if you do phone_column = '() -' then it will only match the exact same string.

Any how, I'm not sure why you want to take this road, while you can validate the current numbers and try to only store the valid ones, as storing invalid numbers would be useless.

I'll give you an example, to validate phone numbers, you first take out any existed formats (parentheses, dashes, spaces) then you'll be end up with a whole number with 10 or 7 digits. depends on your way of storing phone numbers. any numbers less than that would be invalid.

To remove the formats : 

SELECT REPLACE(REPLACE(REPLACE(REPLACE(Number,'(',''),')',''),'-',''),' ','')

Now you will have only numbers, which will be easier to handle.

Like this : 

SELECT 
    phone_column
FROM (SELECT REPLACE(REPLACE(REPLACE(REPLACE(phone_column,'(',''),')',''),'-',''),' ','') phone_column FROM client_table) D

PS : Some countries phone numbers begins with 0, if your numbers don't begin with 0, then you would cast the number to BIGINT, which will remove any leading zeros.

Now, you can use the case to validate the numbers and do whatever you like with them.

SELECT 
    CASE 
        WHEN LEN(phone_column) = 10 
        THEN '(' + SUBSTRING(phone_column,1,3) + ') ' + SUBSTRING(phone_column, 3,3) + '-' + SUBSTRING(phone_column, 6,4) 
        ELSE '(000) 000-0000' 
    END Phone
FROM (SELECT REPLACE(REPLACE(REPLACE(REPLACE(phone_column,'(',''),')',''),'-',''),' ','') phone_column FROM client_table) D

Upvotes: 0

DIWP
DIWP

Reputation: 181

declare @test table(ph varchar(20))

insert into @test 
select '( ) -'
UNION
select ''
UNION
select '(123)-456-7890'

select case 
           when replace(ph,'( ) -','')='' then '000-000' 
           else substring(ph,6,8) 
        end 
from @test

Upvotes: 1

Related Questions