how to update a variable with not null values

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)

how to update a variable with not null values

Author

Topic

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-10-14 : 04:57:10

hi all,
i want to update a variable with not null values whereas i have both null,empty,not null values in my table

can anybody help me to solve this issue

thanks in advance

regards
durgesh j

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-10-14 : 05:06:27

perhaps you should give us some sample data to illustrate what you mean better

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 05:07:29

use coalesce() or isnull() to convert NULL values to some default value

DURGESH
Posting Yak Master

105 Posts

Posted - 2008-10-14 : 05:27:38

hi visakh,
i have a tbl as
col1 col2 col3
1 a aa
4 b bb
2 c
5 d dd
3 e ee

now in my output i want to display the values of a,e,b,d.
Here i have omitted c bcoz the value of c is empty in col3

Output:
a:aa;e:ee;b:bb;d:dd

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-14 : 05:51:09

This would produce those results but is this what you *really* wanted


SELECT
	[colb]
	,[colc]
FROM
	tbl
WHERE
	[colc] IS NOT NULL

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-14 : 05:52:44

Or is your output supposed to be a ; separated string? with colb:colc pairs where colc is not null?

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-14 : 05:58:53

In which case you could do this:


DECLARE @tbl TABLE (
	[cola] INT
	, [colb] VARCHAR(10)
	, [colc] VARCHAR(10)
	)

INSERT @tbl
      SELECT 1, 'a', 'aa'
UNION SELECT 4, 'b', 'bb'
UNION SELECT 2, 'c', NULL
UNION SELECT 5, 'd', 'dd'
UNION SELECT 3, 'e', 'ee'

DECLARE @string VARCHAR(8000)
SET @string = ''

SELECT
	@string = @string + [colb] + ':' + [colc] + ';'
FROM
	@tbl
WHERE
	[colc] IS NOT NULL
ORDER BY
	[cola]

IF LEN(ISNULL(@string, '')) <> 0 SELECT @string = LEFT(@string, LEN(@string) - 1)
SELECT @string

Why do you want to do this? I can't think of a reason.

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources