question

askmlx121 avatar image
askmlx121 asked

empty or null check function in SQL???

Hi I have a table like below

create table #test1(CatTitle_E varchar(10),CatTitle_F varchar(10),CatTitle_G 
varchar(10), CatTitle_J varchar(10))
	insert into #test1
	select 'manoj','diwari','ind','x1'
	union
	select 'ponting',null,null,null
	union
	select null,null,null,null
	union
	select 'balaji','','',''
	union
	select '','','',''
	union
	select '','watson','shane','aus'
	select * from #test1

I want the output to handle NULL or Empty string and replace with value like below

**Condition 1:**

It replaces if the Cattitle F,G,J have any value we have to put that value

**Condition 2:**

but if Cattitle F,G,J have null or empty we have to put Cattitle_E value

Sample Output looks like For If We consider CatTitle_E and CatTitle_F only:(I need same as remaining)see pic below:

![alt text][1]

I searched in Any function related to handle in empty value in SQL it is not available.

Only we have **Coalesce(),Nullif(),Isnull()** functions are only available.

**So Is any one to help me to achieve this using function or UDF function in SQL?????**

[1]: /storage/temp/663-op.jpg

sql-server-2005nullfunction
op.jpg (11.3 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AllenKinsel avatar image
AllenKinsel answered
In your one column example

SELECT  CASE ISNULL(CatTitle_F, '')
          WHEN '' THEN CatTitle_E
          ELSE CatTitle_F
        END
FROM    #test1
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Nice its good its working fine.................thanking you Allenkinsel
0 Likes 0 ·
BrandieTarvin avatar image
BrandieTarvin answered
Use the CASE statement with the ISNULL() function and you should get what you want.
7 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Grant Fritchey: it is actually his question, both askmlx121 and Ashok Sqltechi are linking to the same profile. I've actually noticed this before, and its really confusing. For some reason, the name displayed in the Q/A parts are different than the names in the comments. Perhaps some engine setting that may "correct" this inconsistency?
2 Likes 2 ·
@askmlx121 since it's not your question, you can't mark the answer. You can only vote up or down on answers that are not in your own question.
1 Like 1 ·
Odd. That's really odd.
1 Like 1 ·
Thanking you fur your immediate suggestion. Could you provide the script it might impact sure.
0 Likes 0 ·
If you provide i will immediately mark as you a answer.......
0 Likes 0 ·
Show more comments
Valentino Vranken avatar image
Valentino Vranken answered
Here's what I would do: with CatTitles as ( --replace empty string with NULL select case when LEN(CatTitle_E) = 0 then null else CatTitle_E end CatTitle_E , case when LEN(CatTitle_F) = 0 then null else CatTitle_F end CatTitle_F , case when LEN(CatTitle_G) = 0 then null else CatTitle_G end CatTitle_G , case when LEN(CatTitle_J) = 0 then null else CatTitle_J end CatTitle_J from #test1 ) select * , COALESCE(CatTitle_F, CatTitle_G, CatTitle_J, CatTitle_E) Result from CatTitles Through that Common Table Expression I first take care of the null and empty string mess, then I use COALESCE to get the first non-null value. COALESCE will return the first non-null value in the parameter list, so if the order is not what you'd expected then you'll need to change it there. **Edit:** based on the implementation of the UDF as posted by OP (the specs aren't 100% clear), here's a possible alternative: with CatTitles as ( --replace empty string with NULL select case when LEN(CatTitle_E) = 0 then null else CatTitle_E end CatTitle_E , case when LEN(CatTitle_F) = 0 then null else CatTitle_F end CatTitle_F , case when LEN(CatTitle_G) = 0 then null else CatTitle_G end CatTitle_G , case when LEN(CatTitle_J) = 0 then null else CatTitle_J end CatTitle_J from #test1 ) select * , COALESCE(CatTitle_F, CatTitle_E) CatTitle_F2 , COALESCE(CatTitle_G, CatTitle_E) CatTitle_G2 , COALESCE(CatTitle_J, CatTitle_E) CatTitle_J2 from CatTitles
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If you vote down, at least have the guts to explain why!!!
0 Likes 0 ·
Yes, I totally agree that the reason to downvote should have been provided. This way we keep ourselves honest. But in this case, I guess for both the values NULL and empty string, the result would be NULL? which might not be the requirement? (Unless the question requirement has been changed after the answer is provided. Which makes me believe that any editing should be highlighted somehow/somewhere, so everyone knows about it). But personally, I do not think that this solution deserves a downvote. No way. So +1 from here.
0 Likes 0 ·
@Usman Butt: thanks, I appreciate that! I've also added an alternative solution to take the additional "requirements" derived from the UDF into account.
0 Likes 0 ·
As you posted first script is wrong so i gave -1 but you posted second script is correct so i gave +1 Thanking you with CatTitles as ( --replace empty string with NULL select case when LEN(CatTitle_E) = 0 then null else CatTitle_E end CatTitle_E , case when LEN(CatTitle_F) = 0 then null else CatTitle_F end CatTitle_F , case when LEN(CatTitle_G) = 0 then null else CatTitle_G end CatTitle_G , case when LEN(CatTitle_J) = 0 then null else CatTitle_J end CatTitle_J from #test1 ) select *,COALESCE(CatTitle_F, CatTitle_G, CatTitle_J, CatTitle_E) as Result from CatTitles its wrong but with CatTitles as ( --replace empty string with NULL select case when LEN(CatTitle_E) = 0 then null else CatTitle_E end CatTitle_E , case when LEN(CatTitle_F) = 0 then null else CatTitle_F end CatTitle_F , case when LEN(CatTitle_G) = 0 then null else CatTitle_G end CatTitle_G , case when LEN(CatTitle_J) = 0 then null else CatTitle_J end CatTitle_J from #test1 ) select * , COALESCE(CatTitle_F, CatTitle_E) CatTitle_F2 , COALESCE(CatTitle_G, CatTitle_E) CatTitle_G2 , COALESCE(CatTitle_J, CatTitle_E) CatTitle_J2 from CatTitles it correct Thanking you
0 Likes 0 ·
@askmlx121: thanks for the explanation. Just a small tip of advice: it's more appreciated if you post a comment *why* something doesn't work as expected instead of voting down without explanation - as has been proven here. That way the person that's trying to help you gets a fair chance at... well, helping you. Thanks for correcting it though!
0 Likes 0 ·
askmlx121 avatar image
askmlx121 answered

After Analyse I have created one user Defined function

CREATE FUNCTION dbo.EmptyorNullcheck(@text NVARCHAR(4000),@text1 NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
declare @Return varchar(300)
 IF ISNULL(@text,'')='' or @text is null     
begin
     select @Return=@text1    
end
else if @text is not null
select @Return=@text
return @return
END
Query for Result:
select CatTitle_E[E],dbo.EmptyorNullcheck(CatTitle_F,CatTitle_E)
[rF],CatTitle_F[F],dbo.EmptyorNullcheck(CatTitle_J,CatTitle_E)[rJ],CatTitle_J[J]
from #test1

Its also Give the Correct result Too!!!!!!!!!!!

2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Ashok There is no need to make it a scalar UDF. If I were you I would have kept it an inline TVF i.e. same like the accepted answer.
3 Likes 3 ·
ok thanks usman..butt. I just gave another method. but best way is accepted answer only
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.