Count Fields having valid values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Count Fields having valid values

Author

Topic

bmsra79
Starting Member

24 Posts

Posted - 2012-09-10 : 10:33:36

Hi,
I have a table like this:

Col1, Col2, Col3, Col4, Col5, Status
-------------------------------------
Not Provided, 1, <3, ND, 5,
NULL, NULL, NULL, 4, 1,
NULL, NULL, NULL, NULL, NULL,

I need to populate the STATUS column as follows:

Col1, Col2, Col3, Col4, Col5, Status
------------------------------------
Not Provided, 1, <3, ND, 5, 3 points captured
NULL, NULL, NULL, 4, 1, 2 points captured
NULL, NULL, NULL, NULL, NULL, 0 points captured

STATUS column = # of valid numeric values entered in any of the 5 columns before it.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-10 : 10:51:49

Something along these lines, although it is not clear to me if the <3 in the first row in your sample was a typo, or whether it was considered a valid numeric character. I am assuming the latter.

SELECT *,CAST(
	CASE WHEN Col1 LIKE '%[^0-9<]%' THEN 0 ELSE 1 END +
	CASE WHEN Col2 LIKE '%[^0-9<]%' THEN 0 ELSE 1 END +
	CASE WHEN Col3 LIKE '%[^0-9<]%' THEN 0 ELSE 1 END +
	CASE WHEN Col4 LIKE '%[^0-9<]%' THEN 0 ELSE 1 END +
	CASE WHEN Col5 LIKE '%[^0-9<]%' THEN 0 ELSE 1 END AS VARCHAR(32)) +
	' points captured' AS [Status]
FROM YourTable;

bmsra79
Starting Member

24 Posts

Posted - 2012-09-10 : 11:38:45

The <3 is a valid value here.

I suppose you meant
CASE WHEN Col1 LIKE '%[^0-9<]%' THEN 1 ELSE 0
here.

But, this Col1 LIKE '%[^0-9<]%' condition does not always give me accurate results for text containing numeric values.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-10 : 11:49:54

quote:
Originally posted by bmsra79

The <3 is a valid value here.

I suppose you meant
CASE WHEN Col1 LIKE '%[^0-9<]%' THEN 1 ELSE 0
here.

But, this Col1 LIKE '%[^0-9<]%' condition does not always give me accurate results for text containing numeric values.

No, I meant the other way around, which is what I had posted. However, what I posted would not handle nulls correctly. You need to change it as shown below:

SELECT *,CAST(
	CASE WHEN COALESCE(Col1,'X') LIKE '%[^0-9<]%' THEN 0 ELSE 1 END +
	CASE WHEN COALESCE(Col2,'X') LIKE '%[^0-9<]%' THEN 0 ELSE 1 END +
	CASE WHEN COALESCE(Col3,'X') LIKE '%[^0-9<]%' THEN 0 ELSE 1 END +
	CASE WHEN COALESCE(Col4,'X') LIKE '%[^0-9<]%' THEN 0 ELSE 1 END +
	CASE WHEN COALESCE(Col5,'X') LIKE '%[^0-9<]%' THEN 0 ELSE 1 END AS VARCHAR(32)) +
	' points captured' AS [Status]
FROM YourTable;

bmsra79
Starting Member

24 Posts

Posted - 2012-09-10 : 12:25:53

If there is an alpha numeric combination (or) float value, this does not seem to work.

Examples: 13K, 13.5

By the way, this code works in all other cases...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-10 : 13:15:37

You can add whatever characters you want to consider as valid into the like clause. So for example

CASE WHEN COALESCE(Col1,'X') LIKE '%[^0-9<K.]%' THEN 0 ELSE 1 END +

But then, of course, a value such as 'KKKK' or '....' will also pass that test and be counted as a number.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources