write a query to get count of all fields named LX where x is a number

Question

Post reply

write a query to get count of all fields named LX where x is a number

fabriziodb

Mr or Mrs. 500

Points: 562
More actions
January 29, 2015 at 2:49 am

#314185

for example in a table with this fields "field1, L1,L3,L100" field2 the count is 3
it would be better to match a number into the like but i thinks it cannot be done in the like so i've to add another condition to ensure all the text after L is a number.
is this the best way to do it?
Select count(*) from Information_Schema.Columns Where Table_Name = @Table
AND column_name like 'L%' and ISNUMERIC(SUBSTRING(column_name,2, len(column_name)-1))=1

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

ISNUMERIC returns surprising results and it is not the best way to check for a number.

Try this instead:

SELECT *

FROM (

VALUES('L1'),('L2'),('L100'),('Field1'),('Field2')

) AS v(name)

WHERE name LIKE 'L%' -- starts with L

AND SUBSTRING(name,2,LEN(name)) NOT LIKE '%[^0-9]%' -- is numeric

-- Gianluca Sartori

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

Quick solution which excludes any columns with a character after the column prefix

😎

SET NOCOUNT ON;

USE tempdb;

GO

IF OBJECT_ID(N'dbo.TBL_COUNT_COLUMNS') IS NOT NULL DROP TABLE dbo.TBL_COUNT_COLUMNS;

CREATE TABLE dbo.TBL_COUNT_COLUMNS

(

field1 INT NULL

,L1 INT NULL

,L3 INT NULL

,L100 INT NULL

,L10B INT NULL

);

DECLARE @COL_PREFIX NVARCHAR(128) = N'L';

DECLARE @OBJECT_ID INT = OBJECT_ID(N'dbo.TBL_COUNT_COLUMNS');

SELECT

COUNT(*) AS COL_COUNT

FROM sys.columns C

WHERE C.object_id = @OBJECT_ID

AND C.name LIKE CONCAT(@COL_PREFIX,N'[0-9]%')

AND C.name NOT LIKE CONCAT(@COL_PREFIX,N'%[A-Z,a-z]%');

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Eirikur Eiriksson (1/29/2015)
Quick solution which excludes any columns with a character after the column prefix
...
NOT LIKE CONCAT(@COL_PREFIX,N'%[A-Z,a-z]%');

I don't think this is enough to exclude symbols and other characters not in the A-Z range.

Am I missing something?

-- Gianluca Sartori

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 4

spaghettidba (1/29/2015)
Eirikur Eiriksson (1/29/2015)
Quick solution which excludes any columns with a character after the column prefix
...
NOT LIKE CONCAT(@COL_PREFIX,N'%[A-Z,a-z]%');
I don't think this is enough to exclude symbols and other characters not in the A-Z range.
Am I missing something?

Don't think so, your's is a better solution excluding everything that is not a number.

😎

fabriziodb Mr or Mrs. 500 Points: 562 More actions · Answer 5

fabriziodb

Mr or Mrs. 500

Points: 562

January 29, 2015 at 6:31 am

#1773961

thanks, get spaghetti code 🙂

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

fabriziodb (1/29/2015)
for example in a table with this fields "field1, L1,L3,L100" field2 the count is 3
it would be better to match a number into the like but i thinks it cannot be done in the like so i've to add another condition to ensure all the text after L is a number.
is this the best way to do it?
Select count(*) from Information_Schema.Columns Where Table_Name = @Table
AND column_name like 'L%' and ISNUMERIC(SUBSTRING(column_name,2, len(column_name)-1))=1

Shifting gears a bit, you can now see what happens when you store such denormalized data and it will only continue to be a pain in the future. My recommendation would be to normalize the data by splitting "Field1" up into separate rows with the appropriate key, as well. A whole lot of code will become much simpler after that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)