sql query prob

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » sql query prob

15 posts, Page 1 of 2

1 2 »»

sql query prob

Rate Topic

Display Mode

Topic Options

Author

Message

asranantha

Posted Sunday, August 26, 2012 7:46 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 162, Visits: 3,820

hi friends i have a small doubt in sql plz solve
i have a table that table contains some nullt values and some spaces.
how to seperate what ever contains null values colums and space values columns in sql table

table data like id ,name , sal
1 ,abc ,100
2 ,ravi ,null
,venu ,200
3 , ,600
4 , vnky ,3600
5 ,null ,4500
null,fanu ,3600
52,lion ,
25 , ,3520
30,null ,1000

based on this table i want find which columnss contains null values and which columns contains space values

output like

nullvalues spacevalues
2 ,ravi ,null , venu ,200
null,fanu ,3600 3 , ,600
30,null ,1000 52 ,lion ,
25 , ,3520

and iam try this output like

select * from tablename where id is null or name is null or sal is null
select * from tablename where id=' ' or name =' ' or sal = ' '
but i want executste this output in one query .flexibls output.
plz tell me that query

Post #1350112

dwain.c

Posted Sunday, August 26, 2012 8:07 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:07 PM
Points: 2,340, Visits: 3,167

Try:

select * 
from tablename 
where RTRIM(ISNULL(id, '')) = '' or RTRIM(ISNULL(name, '')) = '' OR RTRIM(ISNULL(sal, '')) = ''

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1350178

asranantha

Posted Sunday, August 26, 2012 10:19 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 162, Visits: 3,820

hi try this logic but in sal columns null values not retriveing
select *
from aaaa
where RTRIM(ISNULL(id, '')) = '' or
RTRIM(ISNULL(name, '')) = '' OR RTRIM(ISNULL(sal, '')) = ''

that records is 4 lo NULL

and plz exp how that logic RTRIM(ISNULL(id, '')) = '' how its checking.
plz tell me

Post #1350189

dwain.c

Posted Sunday, August 26, 2012 10:29 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:07 PM
Points: 2,340, Visits: 3,167

Can you post some DDL and sample data so I can check?

It may depend on the type of your sal column.

RTRIM(ISNULL(abc, '')) = ''

The above code converts abc (character string) to an empty string first (ISNULL) and then trims any trailing blanks (RTRIM) so that the result if NULL, '' or ' ' is an empty character string.

I suggest you Google on SQL ISNULL and SQL RTRIM.

Post #1350191

rhythmk

Posted Sunday, August 26, 2012 10:36 PM

Old Hand

Group: General Forum Members
Last Login: Saturday, November 10, 2012 9:30 AM
Points: 388, Visits: 603

asranantha (8/26/2012)

Please post the DDL of table.
What are the data type of table columns ?

--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Smile

Post #1350193

asranantha

Posted Monday, August 27, 2012 2:13 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 162, Visits: 3,820

data typeslike id int,name varchar(50),sal int

Post #1350244

demonfox

Posted Monday, August 27, 2012 2:14 AM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, May 18, 2013 6:46 PM
Points: 1,074, Visits: 1,076

asranantha (8/26/2012)

hi friends i have a small doubt in sql plz solve
i have a table that table contains some nullt values and some spaces.
how to seperate what ever contains null values colums and space values columns in sql table

and iam try this output like

select * from tablename where id is null or name is null or sal is null
select * from tablename where id=' ' or name =' ' or sal = ' '
but i want executste this output in one query .flexibls output.
plz tell me that query

pls post the ddl ;
as , for an Int or float , while inserting emptly data , sql server implicitly convert it to 0 , also in the where clause search

select * from tablename where  id=' '
select * from tablename where   sal = ' '
-- is actually 
select * from tablename where  id=0
select * from tablename where   sal = 0

so first , you better handle data before entering into table for an int or float column;
for an string, IsNull and Rtrim should do.

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one Ermm

Post #1350246

demonfox

Posted Monday, August 27, 2012 2:22 AM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, May 18, 2013 6:46 PM
Points: 1,074, Visits: 1,076

select * from #testtable
where id is null or name is null or sal is null
union all
select * from #TestTable
where name =''

use column name instead of * ;
and I assume other values can't be empty..

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one Ermm

Post #1350249

ben.brugman

Posted Monday, August 27, 2012 2:23 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, May 18, 2013 9:41 AM
Points: 152, Visits: 749

id int,
name varchar(50),
sal int

With the above datatypes you can not have a table:
,venu ,200
null,fanu ,3600

If the id is an int you can not have both a NULL value and a 'non value' or empty string.

For selection you can locate the NULLs with:

id IS

Post #1350251

dwain.c

Posted Monday, August 27, 2012 2:33 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:07 PM
Points: 2,340, Visits: 3,167

Actually you can try this:

select * 
from tablename 
where id IS NULL or RTRIM(ISNULL(name, '')) = '' OR sal IS NULL

Post #1350262

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions