Hi-how to use for loop??? - 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)

Hi-how to use for loop???

Author

Topic

rosedeepa123
Starting Member

40 Posts

Posted - 2006-01-03 : 05:23:41

Hello friends

i need to replace the null values by blank space in my table

so i dont know how to check all cells in my table in c++ or c
we do something like

for (i=0,i<n;i++)
for (j=0;j<m;j++)

if
a[i][j]=etc etc
then
etc...

is ther anything like this in sql??

ie to check from the first cell to the last cell watever is the table??

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-03 : 05:27:50

this will return blank if column have a null value
select isnull(columnname,'') as col1 from tablename

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-01-03 : 05:29:01

[code]
update MyTable
set col1 = isnull(col1, ''),
col2 = isnull(col2, ''),
coln = isnull(coln, '')
[/code]

Go with the flow & have fun! Else fight the flow

rosedeepa123
Starting Member

40 Posts

Posted - 2006-01-03 : 05:30:02

so u mean tat i hv to specify all columns
and write this query for all columsn??
so there is nt an option to do this for any table right?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-01-03 : 05:37:13

no.

you can get the list of all columns from information_schema.columns view.
also set default values to '' for columns for which you don't want to have nulls.

Go with the flow & have fun! Else fight the flow

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-03 : 05:37:32

yeah you need to specify it for all the columns.
but do you want to update null values in all the tables in ur db
or
you just need to show blank in your presentation layer if there is null value

rosedeepa123
Starting Member

40 Posts

Posted - 2006-01-03 : 05:41:00

yes i just need to show null as blank space while doing a presentation
or displaying without modifying data in table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-03 : 05:45:47

Then When selecting the columns, use IsNull

Select Isnull(col1,''),Isnull(col2,''),.... from yourTable

If you dont want to name the columns explicitely then use Dynamic SQL

Declare @sql varchar(2000)

select @sql=Isnull(@sql+Column_name+'='+'IsNull('+column_name+','''')'+',','') 
from information_schema.columns where table_name='yourTable'

set @sql='Select '+substring(@sql,1,len(@sql)-1)+' from yourTable'

EXEC(@sql)

Madhivanan

Failing to plan is Planning to fail

rosedeepa123
Starting Member

40 Posts

Posted - 2006-01-03 : 05:47:35

Otherwise noone has a solution for the loop problem???

Tankx for the null to blank conversion
i needed to know whether we cud do it within a loop so tat it will perform the conversion watever the number of rows n columns is.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-01-03 : 05:55:00

if you want to do this in your front end you have
do an
if (cellvalue == null)
{
cellvalue = ""
}

when you're displaying data. so cellvalue is your recordset("columnname OR columnIndex").value

Go with the flow & have fun! Else fight the flow

rosedeepa123
Starting Member

40 Posts

Posted - 2006-01-03 : 06:19:04

Declare @sql varchar(2000)
select @sql=Isnull(@sql+Column_name+'='+'IsNull('+column_name+','''')'+',','')
from information_schema.columns where table_name='authors'
set @sql='Select '+substring(@sql,1,len(@sql)-1)' from authors'
EXEC(@sql)

at the last sentence its showing error
any probs with the quotes?? why select is given in quotes???

rosedeepa123
Starting Member

40 Posts

Posted - 2006-01-03 : 06:31:53

Can u explain me these quotes if u dont mind???
i am a begginer in sql server so tat wll b a great help if u tell me details of this code
tank u in advance

rose

quote:
Originally posted by madhivanan

Then When selecting the columns, use IsNull

Select Isnull(col1,''),Isnull(col2,''),.... from yourTable

If you dont want to name the columns explicitely then use Dynamic SQL

Declare @sql varchar(2000)

select @sql=Isnull(@sql+Column_name+'='+'IsNull('+column_name+','''')'+',','')
from information_schema.columns where table_name='yourTable'

set @sql='Select '+substring(@sql,1,len(@sql)-1)+' from yourTable'

EXEC(@sql)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-03 : 06:32:02

You omitted +

Declare @sql varchar(2000)
select @sql=Isnull(@sql+Column_name+'='+'IsNull('+column_name+','''')'+',','')
from information_schema.columns where table_name='authors'
set @sql='Select '+substring(@sql,1,len(@sql)-1)+' from authors'
EXEC(@sql)

Madhivanan

Failing to plan is Planning to fail

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-01-03 : 06:35:07

Check Madhivanan's answer. You're missing


set @sql='Select '+substring(@sql,1,len(@sql)-1) +' from authors'

May I say, that you should get and read a basic's book about SQL and SQL Server?

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-01-03 : 06:35:53

Òops, strange things happen while typing.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

rosedeepa123
Starting Member

40 Posts

Posted - 2006-01-03 : 06:39:02

hey frank!!!

Sorry if i wasted ur time
but im a begginer in sql server so i hv a looooottt of doubts and i hv nobody to depend on or ask help
tats y

anyway its my need so ii hv to ask even if u people make fun of me

rose

rosedeepa123
Starting Member

40 Posts

Posted - 2006-01-03 : 06:46:29

tanx yaar for ur help
it works
tank a loottt

rose

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2006-01-03 : 06:48:36

Don't get me wrong. I was not try to make a fool out of you. If you felt insulted, I apologise!
I was serious about suggesting a book about databases. Everyone was a beginner at some point in time.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-03 : 06:50:15

Also you can use IIF(isnull(col),'',col) in the Presentation layer

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-03 : 06:52:50

>>anyway its my need so ii hv to ask even if u people make fun of me

There is no fun on what Frank said

Start with these
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

rosedeepa123
Starting Member

40 Posts

Posted - 2006-01-03 : 06:59:21

tanks

ok!! if i took franks comments in a bad sense, im sorry for tatok frank.????
sorry

actually im doing a course in programming,but its a self coaching course

so teachers left us alone in one class ,and after each week they come to put tests!!!!!!(without teaching us a single word!!)

so im tensed ,and tats y im asking doubts which my sound silly to u all as ur experts now in this field!!

ok. so i wll always harass u with my questions!!
tanks for the tutorials

And one mor thing to madhivanan u r from india right? where r u working?
im from kerala n did my MCA in tamilnadu at ksr college erode!!do u know tat college??

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources