SQL column names and quotename

Question

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.

score 2 · Answer 1 · edited Jun 08 '15 at 12:29

2

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_%]')

edited Jun 08 '15 at 12:29

Patrick Hofman

148,824
21
237
306

answered Jun 08 '15 at 12:17

Gordon Linoff

1,198,228
53
572
709

This doesn't print out system column - it prints out non system column. – aydjay Jun 08 '15 at 12:25
@d347hm4n . . . This followed the logic in the question, before the question was modified. – Gordon Linoff Jun 08 '15 at 12:29
@GordonLinoff: I don't see the question updated in the mean-time. Never mind, I fixed the code issue. If you want to revert, feel free. – Patrick Hofman Jun 08 '15 at 12:31

score 1 · Accepted Answer · edited May 23 '17 at 12:14

1

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'

edited May 23 '17 at 12:14

Community

1
1

answered Jun 08 '15 at 12:18

Patrick Hofman

148,824
21
237
306

This doesn't print out system column - it prints out non system column – aydjay Jun 08 '15 at 12:27
You can also use another character in quotename. QUOTENAME('DW_ID', '' this could make the like comparison a little easier. – Tom Jun 08 '15 at 12:31
@Tom: Okay, nice suggestion. – Patrick Hofman Jun 08 '15 at 12:32

SQL column names and quotename

2 Answers2