CODEWITHSUNDEEP
sql-servert-sqlspecial-characters
Mahdi Hesari
Mahdi Hesari

Reputation: 337

How to use special characters in SQL Server variables?

I'm trying to declare a variable like @wi-fi in SQL Server but dash character in not allowed. There are other characters like / * - ( ) with same problem. I know for table or column name we can put it in the [] but what about declaring variables? is there a solution?

I have already searched the web but couldn't find any way.

Upvotes: 3

Views: 5379

Answers (2)

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6564

Local variable names must comply with the rules for identifiers. MSDN says that:

When identifiers are used in Transact-SQL statements, the identifiers that do not comply with these rules must be delimited by double quotation marks or brackets.

But this rule does not apply to variable naming. In SQL Server there is a restriction on variable names. All variable names must begin with a single @ sign. After that the variable name must follow the rules for identifiers and can contain a total of 128 characters. When we say characters, we mean that the name can contain:

  • Letters
  • Numbers
  • At sign @
  • Pound sign #
  • Dollar sign $
  • Underscore character _

The variable name cannot contain any dash or spaces.

Upvotes: 14

Rich L
Rich L

Reputation: 21

Some context to answer people such as @Alex who need to know the "why" for questions like this:

I ran into a similar problem, I needed to use just a small piece of a URL saved in my db where the front and ends were irrelevant.

I first attempted to use:

declare @variable varchar(250) = %x%; 
SELECT * FROM tblone WHERE column1 LIKE '@variable'

however this returned error: Arithmetic overflow error converting numeric to data type varchar

My working query was formatted:

declare @variable varchar(1000) = x; 
SELECT * FROM tblone WHERE column1 LIKE '%'+@variable+'%'

Upvotes: 0

Related Questions