August 26, 2012 at 7:46 am
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
August 26, 2012 at 8:07 pm
Try:
select *
from tablename
where RTRIM(ISNULL(id, '')) = '' or RTRIM(ISNULL(name, '')) = '' OR RTRIM(ISNULL(sal, '')) = ''
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 26, 2012 at 10:19 pm
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 4loNULL
and plz exp how that logic RTRIM(ISNULL(id, '')) = '' how its checking.
plz tell me
August 26, 2012 at 10:29 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 26, 2012 at 10:36 pm
asranantha (8/26/2012)
hi friends i have a small doubt in sql plz solvei 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
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
August 27, 2012 at 2:13 am
data typeslike id int,name varchar(50),sal int
August 27, 2012 at 2:14 am
asranantha (8/26/2012)
hi friends i have a small doubt in sql plz solvei 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:
August 27, 2012 at 2:22 am
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:
August 27, 2012 at 2:23 am
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
August 27, 2012 at 2:33 am
Actually you can try this:
select *
from tablename
where id IS NULL or RTRIM(ISNULL(name, '')) = '' OR sal IS NULL
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 27, 2012 at 2:44 am
CREATE TABLE tablename
(
id int NULL,
name varchar(50) NULL,
val int NULL
)
insert into tablename VALUES (1 ,'abc' ,100)
insert into tablename VALUES (2 ,'ravi' ,null)
insert into tablename VALUES ('','venu' ,200)
insert into tablename VALUES (3 ,'',600)
insert into tablename VALUES (4 , 'vnky' ,3600)
insert into tablename VALUES (5 ,'null' ,4500 )
insert into tablename VALUES (null,'fanu' ,3600)
insert into tablename VALUES (52,'lion' ,'')
insert into tablename VALUES (25 ,'' ,3520)
insert into tablename VALUES (30,null ,1000)
insert into tablename VALUES (999,'null' ,999)
insert into tablename VALUES (1999,'NULL' ,1999)
insert into tablename VALUES (2999,' ' ,2999)
insert into tablename VALUES (3999,' ' ,3999)
-- SELECT ALL ROWS WHERE THE NAME IS NULL OR THE NAME ONLY CONTAINTS SPACES.
select * from tablename where RTRIM(coalesce(name,'')) = ''
select * from tablename where id IS NULL
select * from tablename where val IS NULL
-- SELECT ALL ROWS WHICH HAVE EMPTY OR NULL VALUES.
select * from tablename where id IS NULL OR RTRIM(coalesce(name,'')) = '' OR val IS NULL
select
COALESCE(id,-987654),
COALESCE(name,'THE name IS NULL IN THIS ROW') DETECT_NULL_ROWS,
'>>>>'+COALESCE(name,'')+'<<<<' DETECT_EMPTY_ROWS,
COALESCE(val,-543211),
*
from tablename
drop table tablename
In varchar fields you can have an NULL, an empty field, a field which only contains spaces and a field which contains the string "NULL", the above statements will help to identify those situations.
In int fields the field does containt a value or it is a NULL field, the value can be 0 (zero), but can not be an empty string. The example has been adapted. (the given example was not consistent).
Hope this helps,
ben brugman
August 27, 2012 at 2:45 am
CREATE TABLE tablename
(
id int NULL,
name varchar(50) NULL,
val int NULL
)
insert into tablename VALUES (1 ,'abc' ,100)
insert into tablename VALUES (2 ,'ravi' ,null)
insert into tablename VALUES ('','venu' ,200)
insert into tablename VALUES (3 ,'',600)
insert into tablename VALUES (4 , 'vnky' ,3600)
insert into tablename VALUES (5 ,'null' ,4500 )
insert into tablename VALUES (null,'fanu' ,3600)
insert into tablename VALUES (52,'lion' ,'')
insert into tablename VALUES (25 ,'' ,3520)
insert into tablename VALUES (30,null ,1000)
insert into tablename VALUES (999,'null' ,999)
insert into tablename VALUES (1999,'NULL' ,1999)
insert into tablename VALUES (2999,' ' ,2999)
insert into tablename VALUES (3999,' ' ,3999)
-- SELECT ALL ROWS WHERE THE NAME IS NULL OR THE NAME ONLY CONTAINTS SPACES.
select * from tablename where RTRIM(coalesce(name,'')) = ''
select * from tablename where id IS NULL
select * from tablename where val IS NULL
-- SELECT ALL ROWS WHICH HAVE EMPTY OR NULL VALUES.
select * from tablename where id IS NULL OR RTRIM(coalesce(name,'')) = '' OR val IS NULL
select
COALESCE(id,-987654),
COALESCE(name,'THE name IS NULL IN THIS ROW') DETECT_NULL_ROWS,
'>>>>'+COALESCE(name,'')+'<<<<' DETECT_EMPTY_ROWS,
COALESCE(val,-543211),
*
from tablename
drop table tablename
In varchar fields you can have an NULL, an empty field, a field which only contains spaces and a field which contains the string "NULL", the above statements will help to identify those situations.
In int fields the field does containt a value or it is a NULL field, the value can be 0 (zero), but can not be an empty string. The example has been adapted. (the given example was not consistent).
Hope this helps,
ben brugman
August 27, 2012 at 2:46 am
CREATE TABLE tablename
(
id int NULL,
name varchar(50) NULL,
val int NULL
)
insert into tablename VALUES (1 ,'abc' ,100)
insert into tablename VALUES (2 ,'ravi' ,null)
insert into tablename VALUES ('','venu' ,200)
insert into tablename VALUES (3 ,'',600)
insert into tablename VALUES (4 , 'vnky' ,3600)
insert into tablename VALUES (5 ,'null' ,4500 )
insert into tablename VALUES (null,'fanu' ,3600)
insert into tablename VALUES (52,'lion' ,'')
insert into tablename VALUES (25 ,'' ,3520)
insert into tablename VALUES (30,null ,1000)
insert into tablename VALUES (999,'null' ,999)
insert into tablename VALUES (1999,'NULL' ,1999)
insert into tablename VALUES (2999,' ' ,2999)
insert into tablename VALUES (3999,' ' ,3999)
-- SELECT ALL ROWS WHERE THE NAME IS NULL OR THE NAME ONLY CONTAINTS SPACES.
select * from tablename where RTRIM(coalesce(name,'')) = ''
select * from tablename where id IS NULL
select * from tablename where val IS NULL
-- SELECT ALL ROWS WHICH HAVE EMPTY OR NULL VALUES.
select * from tablename where id IS NULL OR RTRIM(coalesce(name,'')) = '' OR val IS NULL
select
COALESCE(id,-987654),
COALESCE(name,'THE name IS NULL IN THIS ROW') DETECT_NULL_ROWS,
'>>>>'+COALESCE(name,'')+'<<<<' DETECT_EMPTY_ROWS,
COALESCE(val,-543211),
*
from tablename
drop table tablename
In varchar fields you can have an NULL, an empty field, a field which only contains spaces and a field which contains the string "NULL", the above statements will help to identify those situations.
In int fields the field does containt a value or it is a NULL field, the value can be 0 (zero), but can not be an empty string. The example has been adapted. (the given example was not consistent).
Hope this helps,
ben brugman
August 27, 2012 at 3:26 am
Sorry for the repeats. Ben
August 27, 2012 at 5:25 am
You can try this query
declare @nullvalue varchar(1000)
set @nullvalue=''
select @nullvalue=@nullvalue+','+isnull(id,'Null')+','+isnull(Name,'Null')+','+isnull(Sal,'Null') from Table1 where id is null or Name is null or Sal is null
declare @spacevalue varchar(1000)
set @spacevalue=''
select @spacevalue=@spacevalue+id+','+Name+','+Sal from Table1 where id='' or Name='' or Sal=''
select @nullvalue as nullvalue,@spacevalue as spacevalue
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply