CODEWITHSUNDEEP
sql-serversql-server-2008
user3224208
user3224208

Reputation: 1027

handling empty and spaces and null values

I have small doubt in sql server.
My table emp contains data like

id, name
1,abc
2,
3,n
4,ja
5, jied
6,null

To replace null or empty with 9999 and avoid empty spaces I tried query like below

select coalesce(ltrim(rtrim(substring(name,1,5))) ,'') ='' then '9999' 
else substring(ltrim(rtrim(name),1,5) end name
FROM emp

I don't get a result but the following error is shown

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '='.

Upvotes: 0

Views: 3617

Answers (3)

AHiggins
AHiggins

Reputation: 7219

You're missing a few elements from your code: you need to add CASE WHEN, e.g., 

SELECT 
    CASE 
        WHEN coalesce(ltrim(rtrim(substring(name,1,5))) ,'') ='' 
        THEN '9999' 
        ELSE substring(ltrim(rtrim(name)),1,5)
    END name
FROM emp

I'd also recommend you look into a couple of different approaches using the ISNULL and NULLIF functions. For example, you could use ISNULL(NULLIF(LTRIM(RTRIM(name)), ''), '9999'), which would take care of filtering out NULL values as well as blank values in a single statement.

EDIT: not knowing much about your data, I'm also a bit concerned about the fact that your two SUBSTRING() functions are doing different things. In your WHEN clause, you run your LTRIM() and RTRIM() function against the five characters returned by the SUBSTRING() function, thereby theoretically returning less than five characters. However, in your ELSE clause, you run the SUBSTRING() against the already trimmed result of name.

For examples, see the difference between the following two queries:

SELECT LTRIM(RTRIM(SUBSTRING(' blank space here', 1, 5)))
SELECT SUBSTRING(LTRIM(RTRIM(' blank space here')), 1, 5)

If you really need to limit this to the five characters, you should probably do your trimming on the name before you apply the SUBSTRING() function to it, i.e., 

SELECT ISNULL(NULLIF(SUBSTRING(LTRIM(RTRIM(Name)), 1,5), ''), '9999')
FROM  emp

Upvotes: 1

Kiran Hegde
Kiran Hegde

Reputation: 3681

If you want to get '9999' when the name is null or empty, you can also use the following query instead, just another option apart from the above answers

SELECT COALESCE(NULLIF(LTRIM(RTRIM(name)),''),'9999') FROM emp

Upvotes: 0

Jesuraja
Jesuraja

Reputation: 3844

CASE WHEN is missed and also missed ) in ELSE part

SELECT CASE WHEN COALESCE(SUBSTRING(LTRIM(RTRIM(name)),1,5) ,'') = '' THEN '9999' 
ELSE SUBSTRING(LTRIM(RTRIM(name)),1,5) END name
FROM emp

Upvotes: 0

Related Questions