empty or null check function in SQL???

Question

question

askmlx121 asked Feb 14, '13

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-2005 null function

op.jpg (11.3 KiB)

10 |1200

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

Answer 1 · 2013-02-14T12:16:17Z

AllenKinsel answered Feb 14, '13

In your one column example


SELECT  CASE ISNULL(CatTitle_F, '')
          WHEN '' THEN CatTitle_E
          ELSE CatTitle_F
        END
FROM    #test1

1

10 |1200

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

askmlx121 commented · Feb 15, 2013 at 08:24 AM

Nice its good its working fine.................thanking you Allenkinsel

0 ·

Answer 2 · 2013-02-14T11:46:27Z

BrandieTarvin answered Feb 14, '13

Use the CASE statement with the ISNULL() function and you should get what you want.

7

10 |1200

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

Valentino Vranken commented · Feb 14, 2013 at 12:25 PM

@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 ·

Grant Fritchey ♦♦ commented · Feb 14, 2013 at 12:14 PM

@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 ·

Grant Fritchey ♦♦ commented · Feb 14, 2013 at 12:26 PM

Odd. That's really odd.

1 ·

askmlx121 commented · Feb 14, 2013 at 11:48 AM

Thanking you fur your immediate suggestion. Could you provide the script it might impact sure.

0 ·

askmlx121 commented · Feb 14, 2013 at 11:49 AM

If you provide i will immediately mark as you a answer.......

0 ·

Show more comments

Answer 3 · 2013-02-14T12:20:40Z

Valentino Vranken answered Feb 14, '13

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

10 |1200

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

Valentino Vranken commented · Feb 17, 2013 at 06:57 PM

If you vote down, at least have the guts to explain why!!!

0 ·

Usman Butt commented · Feb 17, 2013 at 08:15 PM

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 ·

Valentino Vranken commented · Feb 18, 2013 at 10:17 AM

@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 ·

askmlx121 commented · Feb 18, 2013 at 10:57 AM

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 ·

Valentino Vranken commented · Feb 18, 2013 at 12:13 PM

@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 ·

Answer 4 · 2013-02-15T08:26:38Z

askmlx121 answered Feb 15, '13

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

10 |1200

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

Usman Butt commented · Feb 17, 2013 at 08:20 PM

@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 ·

askmlx121 commented · Feb 18, 2013 at 10:51 AM

ok thanks usman..butt. I just gave another method. but best way is accepted answer only

0 ·

question