CODEWITHSUNDEEP
sqlsql-serverselectsyntax-errorsql-server-2014-express
Matt
Matt

Reputation: 33

Msg 102, Level 15, State 1, Line 6 Incorrect syntax near 'Reference'

I'm sure I'm missing something here, and could use a hand. The error message I'm getting is in the title.

I've got a column called Agent Reference with results as 80008_23456 etc etc, the first part is the same (80008) for every row with only the information after the '_' being different, I need to split the data so that I can join it to another table using the unique id. Hence the case when statement below, but I'm getting the error above and need some fresh eyes.

select 
    case when CHARINDEX('_',Agent Reference)>0 
         then SUBSTRING(Agent Reference,1,CHARINDEX('_', Agent Reference)-1) 
         else Agent Reference end, 
    CASE WHEN CHARINDEX('_',Agent Reference)>0 
         THEN SUBSTRING(Agent Reference,CHARINDEX('_',Agent Reference)+1,len(Agent Reference))  
         ELSE NULL END as Web_ID
from [Copy of RM_property_performance_report_export-TABLE];
go

Upvotes: 3

Views: 8906

Answers (1)

Mureinik
Mureinik

Reputation: 311053

Spaces aren't allowed in object names. If you want to to use a name like "Agent Reference" you should escape it by surrounding it in square brackets: [Agent Reference].

Upvotes: 3

Related Questions