Replacing empty string in SQL using SELECT

Question

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_

If you have a query that is not working correctly you need to show it to us so we can tell you what is wrong with it. — takendarkk
– takendarkk, Commented Aug 24, 2018 at 17:45
I recommend against storing formatting for phone numbers as data in the database. It should be a mask applied by your application. — UnhandledExcepSean
– UnhandledExcepSean, Commented Aug 24, 2018 at 17:48
I think the best option here would be to use regular expressions to find all possible combinations. Unfortunately, I've never been successfull at using them in SQL Server. It seems to me the implementaion there is not standard. — The Impaler
– The Impaler, Commented Aug 24, 2018 at 18:04
@TheImpaler that is because sql server does not support regular expressions. It does allow for rudimentary pattern matching with syntax that looks similar to regex. — Sean Lange
– Sean Lange, Commented Aug 24, 2018 at 18:23

DIWP · Accepted Answer · 2018-08-24 18:37:36Z

1

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

edited Aug 24, 2018 at 18:37

answered Aug 24, 2018 at 18:04

DIWP

1815 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mark Kram Over a year ago

What if the source data has a null value your solution would fail.

DIWP Over a year ago

OP has specified he has no NULL. Anyway, its a simple case of adding ISNULL.

Sean Lange Over a year ago

The first part of your case expression is returning the string literal 'value' which I don't think is what you intended and it certainly isn't what the OP wanted. :) Easy fix of course.

iSR5 · Accepted Answer · 2018-08-24 19:03:54Z

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

Collectives™ on Stack Overflow

Replacing empty string in SQL using SELECT

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related