How to use like operator to get multiple columns?

Question

How to use like operator to get multiple columns?

subramanyammaruthi

SSC Veteran

Points: 275
More actions
September 12, 2015 at 4:20 am

#307616

Hello!!
Can any one please let know how to use like operator select statement to retrieve multiple column names in sql server DB
for ex: I have a table say employees where in I want to get all column names like emp_,acc_ etc using '%'
And what is this below query used for?
SELECT column_name as 'Column Name', data_type as 'Data Type',
character_maximum_length as 'Max Length'
FROM information_schema.columns
WHERE table_name = 'tblUsers'

Viewing 3 posts - 1 through 2 (of 2 total)

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

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 1

subramanyammaruthi (9/12/2015)
And what is this below query used for?
SELECT column_name as 'Column Name', data_type as 'Data Type',
character_maximum_length as 'Max Length'
FROM information_schema.columns
WHERE table_name = 'tblUsers'

Try running it ... if 'tblUsers' is not a table in your database replace that with the name of an existing table

Some specific permissions are needed to access some of the information_schema views

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 2

subramanyammaruthi (9/12/2015)
Hello!!Can any one please let know how to use like operator select statement to retrieve multiple column names in sql server DBfor ex: I have a table say employees where in I want to get all column names like emp_,acc_ etc using '%' And what is this below query used for?SELECT column_name as 'Column Name', data_type as 'Data Type',character_maximum_length as 'Max Length'FROM information_schema.columns WHERE table_name = 'tblUsers'

The query simply returns the column names and their data types from a table called tblUsers.For the 1st question... how to find all columns in the employees table that have names that begin with "emp_" or "acc_", the query looks like this...

SELECT c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.ORDINAL_POSITION, c.COLUMN_DEFAULT, c.IS_NULLABLE, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, c.CHARACTER_OCTET_LENGTH, c.NUMERIC_PRECISION, c.NUMERIC_PRECISION_RADIX, c.NUMERIC_SCALE, c.DATETIME_PRECISION, c.CHARACTER_SET_CATALOG, c.CHARACTER_SET_SCHEMA, c.CHARACTER_SET_NAME, c.COLLATION_CATALOG, c.COLLATION_SCHEMA, c.COLLATION_NAME, c.DOMAIN_CATALOG, c.DOMAIN_SCHEMA, c.DOMAIN_NAME FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME = 'employees' AND ( c.COLUMN_NAME LIKE 'emp[_]%' -- note that underscores a single character wildcards OR c.COLUMN_NAME LIKE 'acc[_]%' -- to include them as litterals in the search, you must "escape" then using sqiare brackes );