Best approach to fill in cell gaps

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

General SQL Server Forums

New to SQL Server Programming

Best approach to fill in cell gaps

Author

Topic

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2012-07-15 : 15:35:10

Hi

I'm new to working with SP's, so I hope this makes sense.

I'd like to run a search on all rows within table, and if cell 'test1' is null or '', add the word 'test'.

In the same row, I have a cell called 'test2', and if this is null or '', I'd like to add a string, but in this instance, I'd like to add a random word which can be any word from a separate table (tbl_Words) containing 100 random words.

Hope this makes sense.

Thanks as always

Richard Law

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-15 : 17:05:40

What do you mean by cells? Are you using Excel or are you meaning to say a value of a column in a row?

Show us some sample data for starters.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2012-07-15 : 17:29:26

Hi

I'm using MS SQL 2005.

Within the table, there are likely to be cells with empty data. In the first cell (column 'Test1', if it's empty, just add the word 'test'. In column 2, if the cell is empty, I'd like to add a random value from a different table (like a selection of passwords).

Does this make sense?

Thanks for any help

Richard Law

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-15 : 17:38:06

Something like this:

SELECT
	COALESCE(NULLIF(a.test1,''),'test') AS ModifiedTest1,
	COALESCE(NULLIF(a.test2,''),b.word) AS ModifiedTest2
FROM
	YourTable a
	CROSS APPLY 
	( SELECT TOP 1 c.word FROM tbl_words c ORDER BY NEWID()) b

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2012-07-15 : 17:56:18

Fantastic, thank you. I'll start to play with this as a starter for 10.

Thank you again.

quote:

Originally posted by sunitabeck

Something like this:

SELECT
	COALESCE(NULLIF(a.test1,''),'test') AS ModifiedTest1,
	COALESCE(NULLIF(a.test2,''),b.word) AS ModifiedTest2
FROM
	YourTable a
	CROSS APPLY 
	( SELECT TOP 1 c.word FROM tbl_words c ORDER BY NEWID()) b

Richard Law

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources