Click here to Skip to main content
15,125,584 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I want to verify below column's data according defined datatype and null values.

Column1 numeric(18,8) null

Column2 Varchar(10) not null

Column3 Int not null

Column4 int null

column5 bit not null

column6 bit null

column7 datetime null



-- How to check value in column6 is type bit

What I have tried:

I have tried--

SELECT
CASE WHEN Column1 IS NOT NULL AND ISNUMERIC(Column1)<>1 THEN 0 ELSE 1 END AS 'Column1',
CASE WHEN Column2 IS NULL OR Column2='' OR LEN(Column2)>50 THEN 0 ELSE 1 END AS 'Column2',
CASE WHEN ISNULL(Column3,'')='' OR Column3 LIKE '%[^0-9]%' THEN 0 ELSE 1 END AS 'Column3',
CASE WHEN ISNULL(Column4,'')<>'' AND Column4 NOT LIKE '%[^0-9]%' THEN 0 ELSE 1 END AS 'Column4'
CASE WHEN column7 IS NOT NULL AND ISDATE(column7)=0 THEN 50 ELSE 0 END AS 'column7'

FROM TABLE1
Posted
Updated 25-Jul-18 21:01pm
v2
Comments
Kornfeld Eliyahu Peter 26-Jun-18 6:27am
   
If a column is type X and NULL/NOT NULL, you will not be able to insert any other data, so why check?! SQL will throw an exception for you...
suneel kumar gupta 26-Jun-18 7:16am
   
I have column which allow bit value or null value then how should i validate this condition.
Kornfeld Eliyahu Peter 26-Jun-18 7:25am
   
You should not - SQL will, and if the value does not fit you will have an exception!!!
suneel kumar gupta 26-Jun-18 7:33am
   
actually I want to migrate data from another server through scheduler (SQL JOB) and if data validation failed then I want to store in which columns data are invalid.

Lets say I have 5 column and while validate data in 2 column data are invalid than store column name and custom error defined for than column.
Kornfeld Eliyahu Peter 26-Jun-18 7:36am
   
If you are moving from SQL to SQL, than how data can be invalid? Are you moving data from NVARCHAR to INT, or FLOAT to BIT? IF columns in source and target has the same type declaration, than you have fear nothing...
suneel kumar gupta 26-Jun-18 7:38am
   
yes , may be because the data providing from different different vendor.
Kornfeld Eliyahu Peter 26-Jun-18 7:43am
   
Vendor does not matter. AS long as you are moving BIT to BIT and INT to INT...
If you are to CONVERT data you can not have something automatic, but you have to write queries for table-to-table convert...
Mike V Baker 26-Jun-18 10:39am
   
I think the thought there is that the other vendor's database might not be set up with those constraints so suneel is trying to verify the data first.
Mike V Baker 26-Jun-18 10:43am
   
In this case (the column6 bit value) I would probably treat incoming 0 as 0, NULL as NULL, and anything else as 1
I have a question for you, this scheme to validate the incoming data... how do you record that some records didn't pass the validation? Is there an exception report with a TEXT field to contain the incoming raw data?

select isshared,case when isshared = 0 then 'ABC' when isshared=1 then 'XYZ' end as aaa from kk


For my point of view when i understand the question for you.

else there are two way to check for bit filed value

true false
0 1
   
you miss comma for last row
SELECT
CASE WHEN Column1 IS NOT NULL AND ISNUMERIC(Column1)<>1 THEN 0 ELSE 1 END AS 'Column1', 
CASE WHEN Column2 IS NULL OR Column2='' OR LEN(Column2)>50 THEN 0 ELSE 1 END AS 'Column2', 
CASE WHEN ISNULL(Column3,'')='' OR Column3 LIKE '%[^0-9]%' THEN 0 ELSE 1 END AS 'Column3',
CASE WHEN ISNULL(Column4,'')<>'' AND Column4 NOT LIKE '%[^0-9]%' THEN 0 ELSE 1 END AS 'Column4', 
CASE WHEN column7 IS NOT NULL AND ISDATE(column7)=0 THEN 50 ELSE 0 END AS 'column7'

FROM TABLE1
   
Comments
CHill60 25-Jul-18 9:20am
   
The question was "How to check value in column6 is type bit". You are correct about the syntax error but you haven't answered the question asked.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900