Empty string to NULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Empty string to NULL

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-12-20 : 07:59:14

Hi,

I have the following script:

DECLARE @text varchar(2)
SET @text = '  '
SELECT LTRIM(RTRIM(@text))

Is there an easy way to convert this empty string to NULL but to preserve the value if it's not empty? Preferably without using CASE...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-20 : 08:08:37

quote:
Originally posted by harsh_athalye

How about using REPLACE?

DECLARE @text varchar(2)
SET @text = ' '
SELECT @text = replace(LTRIM(RTRIM(@text)), '', null)

select @text

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

That won't work.

Try it with a value of '2 '
he wanted the original value to be preserved if it was not empty

DECLARE @text varchar(2)
SET @text = '2 '
SELECT @text = replace(LTRIM(RTRIM(@text)), '', null)

select @text

This code returns a NULL here

Remember that a Value + NULL = NULL

Lumbago I don't think that you can get away without using CASE here

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-20 : 08:12:00

Ha Ha - Got you Harsh!!

Got the reply with quote in before you deleted it

Duane.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-20 : 08:16:31

Yup!!

I know...I was hoping nobody has seen it before my deleting it!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-12-20 : 08:18:26

Hehe, I got a little confused about where you took that quote from

But if I can't get it to work I'll create a UDF instead...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 08:21:50

What about using CASE?

DECLARE @text varchar(2)
SET @text = ' '
SELECT case when len(@text) = 0 then NULL else LTRIM(RTRIM(@text)) end

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-20 : 08:27:08

or

DECLARE @text varchar(2)
SET @text = ' '
Select NULLIF(LTRIM(RTRIM(@text)),'')

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-20 : 08:27:49

quote:
Originally posted by Peso

What about using CASE?

DECLARE @text varchar(2)
SET @text = ' '
SELECT case when len(@text) = 0 then NULL else LTRIM(RTRIM(@text)) end

Peter Larsson
Helsingborg, Sweden

He wants to avoid using CASE.

Peter - when I saw your reply I was hoping to see some Genius method of getting round the problem without using CASE - You let me down here.

But Lumbago - I don't think that you need to create a UDF just because you want to avoid using CASE, it's not a very complicated case statement. Why do you want to avoid CASE?

Duane.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-12-20 : 08:27:59

quote:
Preferably without using CASE...

This was the specification but I'll create a udf with a case...I have my reasons :)

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-20 : 08:30:42

quote:
Originally posted by madhivanan

or

DECLARE @text varchar(2)
SET @text = ' '
Select NULLIF(LTRIM(RTRIM(@text)),'')

Madhivanan

Failing to plan is Planning to fail

Nice One Maddy - I forgot about NULLIF - probably cos I don't use it that often.

Duane.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 08:40:31

Why even have RTRIM and LTRIM?

DECLARE @text varchar(2)
SET @text = ' '

Select @text, NULLIF(@text, '')

Nice Madhi!

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-20 : 08:44:30

Yes. No need of Trims

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-12-20 : 08:46:55

Oh one more post madhi!!

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-20 : 08:52:02

quote:
Originally posted by Lumbago

Oh one more post madhi!!

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

My second reply
http://sqlteam.com/forums/topic.asp?TOPIC_ID=76552

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 08:55:03

[code]UPDATE m
SET m.PostCount = PostCount + 1
FROM Members AS m (INDEX('Do it in frontend'))
WHERE m.Name = 'Madhivanan'[/code]
(0 row(s) affected)

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-20 : 09:02:57

quote:
Originally posted by Peso

UPDATE m
SET m.PostCount = PostCount + 1
FROM Members AS m (INDEX('Do it in frontend'))
WHERE m.Name = 'Madhivanan'

(0 row(s) affected)

Peter Larsson
Helsingborg, Sweden

Try this

UPDATE m
SET    m.PostCount = PostCount + 1
FROM   Members AS m (INDEX('Do it in the front end application'))
WHERE  m.Name = 'Madhivanan'

(1 row(s) affected)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 09:04:45

I'd say that there is an error in your query, in the scope of this topic.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-20 : 09:12:49

quote:
Originally posted by Peso

I'd say that there is an error in your query, in the scope of this topic.

Peter Larsson
Helsingborg, Sweden

Actually I thought of saying that. But Instead I have given alternate query

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-20 : 09:19:49

One more method without CASE

DECLARE @text char(20)
SET @text = ' '
Select REPLACE(@text,'',NULL)

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-20 : 09:23:12

quote:
Originally posted by madhivanan

One more method without CASE

DECLARE @text char(20)
SET @text = ' '
Select REPLACE(@text,'',NULL)

Madhivanan

Failing to plan is Planning to fail

Madhi - That is the same mistake that Harsh made and then deleted it.

Try it with a value of '2 ' - it replaces the one ' ' with null and then '2' + NULL = NULL. So this doesn't work.

Sorry!

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-20 : 09:38:12

I didnt use proper sample data

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources