CODEWITHSUNDEEP
sql
aydjay
aydjay

Reputation: 868

SQL column names and quotename

I have a query that should be ignoring my applications system columns, I can't explain why it's not detecting the following column as a system column. A system column starts with DW_ and could contain a space, hence the use of quotename.

 DECLARE @columnName NVARCHAR(max)

 set @columnName = Quotename('DW_ID')

 print @columnName

 IF (@columnName LIKE 'DW_%')
    print 'system column'
 else
    print 'non system column'

I would expect the above query to tell me it's a system column but it tells me it's a non system column.

Upvotes: 0

Views: 1291

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

quotename() adds square braces:

select Quotename('DW_ID')

[DW_ID]

Here is a little SQL Fiddle.

So you would want to include these in your pattern matching. Something like:

IF (@columnName LIKE '[[]DW_%]')

Upvotes: 2

Patrick Hofman
Patrick Hofman

Reputation: 156978

First, Quotename adds brackets to DW_ID, which makes that DW_% will never match on [DW_ID].

Second, you should match on the brackets too. Since brackets have a special meaning in a like you need to escape them:

DECLARE @columnName NVARCHAR(max)

set @columnName = Quotename('DW_ID')
-- @columnName is [DW_ID] now

print @columnName

IF (@columnName LIKE '[[]DW_%]')
   print 'system column'
else
   print 'non system column'

Upvotes: 1

Related Questions