SQL Select with Column Name Like

sql select with column name like

You cannot with standard SQL. Column names are not treated like data in SQL.

If you use a SQL engine that has, say, meta-data tables storing column names, types, etc. you may select on that table instead.

how to use LIKE with column name

You're close.

The LIKE operator works with strings (CHAR, NVARCHAR, etc). so you need to concattenate the '%' symbol to the string...

MS SQL Server:

SELECT * FROM table1,table2 WHERE table1.x LIKE table2.y + '%'

Use of LIKE, however, is often slower than other operations. It's useful, powerful, flexible, but has performance considerations. I'll leave those for another topic though :)

EDIT:

I don't use MySQL, but this may work...

SELECT * FROM table1,table2 WHERE table1.x LIKE CONCAT(table2.y, '%')

Select column names based on condition in two separate table and database

You need dynamic SQL for this, and it won't be pretty.

DECLARE @sql     nvarchar(max) = N'SELECT t1.id',
        @sel     nvarchar(max) = N'', 
        @clauses nvarchar(max) = N'';
  
;WITH src(col, name) AS
(
      SELECT QUOTENAME(c.name), c.name 
        FROM Database1.sys.columns AS c
        INNER JOIN Database1.sys.tables AS t
        ON c.[object_id] = t.[object_id]
        WHERE t.name = N'MyTable' AND t.schema_id = 1
      INTERSECT 
      SELECT QUOTENAME(c.name), c.name 
        FROM Database2.sys.columns AS c
        INNER JOIN Database2.sys.tables AS t
        ON c.[object_id] = t.[object_id]
        WHERE t.name = N'MyTable' AND t.schema_id = 1
),
clauses(clause, sel) AS
(
  SELECT clause = char(13) + char(10)
      + N'  OR (   (t1.' + col + N' IS NULL AND t2.' + col + N' IS NOT NULL)'
      + char(13) + char(10) 
      + '       OR (t1.' + col + N' IS NOT NULL AND t2.' + col + N' IS NULL))',
    sel = N',' + char(13) + char(10) 
      + N'  t1_' + name + N' = t1.' + col 
      + N', t2_' + name + N' = t2.' + col
  FROM src
  WHERE name <> N'id'
)
SELECT @sel += sel, @clauses += clause FROM clauses;
  
SELECT @sql += @sel 
   + char(13) + char(10) + N' FROM Database1.dbo.MyTable AS t1'
   + char(13) + char(10) + N' INNER JOIN Database2.dbo.MyTable AS t2'
   + char(13) + char(10) + N' ON t1.id = t2.id AND (1 = 2' + @clauses + N');';
  
SELECT @sql;
--EXEC sys.sp_executesql @sql;

Output (assuming MyTable has common columns id, x, and y):

SELECT t1.id,
  t1_x = t1.[x], t2_x = t2.[x],
  t1_y = t1.[y], t2_y = t2.[y]
 FROM Database1.dbo.MyTable AS t1
 INNER JOIN Database2.dbo.MyTable AS t2
 ON t1.id = t2.id AND (1 = 2
  OR (   (t1.[x] IS NULL AND t2.[x] IS NOT NULL)
       OR (t1.[x] IS NOT NULL AND t2.[x] IS NULL))
  OR (   (t1.[y] IS NULL AND t2.[y] IS NOT NULL)
       OR (t1.[y] IS NOT NULL AND t2.[y] IS NULL)));

How to deal with SQL column names that look like SQL keywords?

Wrap the column name in brackets like so, from becomes [from].

select [from] from table;

It is also possible to use the following (useful when querying multiple tables):

select table.[from] from table;

SQL Column Name wildcard

You will want to build a dynamic query as explained here: https://stackoverflow.com/a/4797728/9553919

SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'Foods'
        AND table_schema = 'YourDB'
        AND column_name LIKE 'Vegetable%'

Select only column name from an sql query

If you want a list of columns, sometimes I use a temporary view:

create view _MyView as <your query here>

Then you can do:

select *
from information_schema.columns
where table_name = '_MyView'

You can get the column names and types by doing this.

Then you can do:

drop view _MyView

(And, of course, the view name should not conflict with anything else.)

---

Related Topics