Find Columns which starts with Lowercase letters in SQL Server Data Base and list them as a SELECT Statement

Question

i am trying to Select All Columns in a Database Which Started With Lower Case Letters . Like 'status'.

i Wrote a Command As Below :

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE LEFT(COLUMN_NAME, 1) = 's'

I Got The First Letter And Checked It IF IT's Equal To LowerCase 's' . But It Returns All Column Name's With 's' and 'S' . i even Tried This :

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE LEFT(COLUMN_NAME, 1) = LOWER('s')

But It Is Like Last one too .

'S' is for Example . i need it for all letters .

Thanks ....

score 2 · Accepted Answer · answered Mar 01 '21 at 12:36

I think the names in information_schema tables are case insensitive. So, your attempt will not just find lower case.

That leaves you with two options. One is to fiddle with collations to get an equivalent case-sensitive collation.

The other is to use the ascii() function:

select *
from information_schema.columns
where ascii(left(column_name, 1)) between ascii('a') and ascii('z');

Here is a db<>fiddle illustrating the difference.

1 Answers1