Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sql query prob Expand / Collapse
Author
Message
Posted Sunday, August 26, 2012 7:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:11 AM
Points: 211, Visits: 4,540
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
Posted Sunday, August 26, 2012 8:07 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
Try:

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




My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1350178
Posted Sunday, August 26, 2012 10:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:11 AM
Points: 211, Visits: 4,540

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
Posted Sunday, August 26, 2012 10:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1350191
Posted Sunday, August 26, 2012 10:36 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, October 6, 2014 6:14 AM
Points: 564, Visits: 888
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

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



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/
Post #1350193
Posted Monday, August 27, 2012 2:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:11 AM
Points: 211, Visits: 4,540
data typeslike id int,name varchar(50),sal int
Post #1350244
Posted Monday, August 27, 2012 2:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
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
Post #1350246
Posted Monday, August 27, 2012 2:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
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
Post #1350249
Posted Monday, August 27, 2012 2:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 5, 2014 11:40 AM
Points: 246, Visits: 1,169


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
Posted Monday, August 27, 2012 2:33 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
Actually you can try this:

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




My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1350262
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse