SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sql query prob


sql query prob

Author
Message
asranantha
asranantha
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 4717
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
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17615 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
asranantha
asranantha
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 4717
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
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17615 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
rhythmk
rhythmk
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1694 Visits: 1099
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/
:-)
asranantha
asranantha
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 4717
data typeslike id int,name varchar(50),sal int
demonfox
demonfox
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2163 Visits: 1192
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
demonfox
demonfox
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2163 Visits: 1192

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
ben.brugman
ben.brugman
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2520 Visits: 2417
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
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17615 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search