SQL Server - Invalid Characters in Parameter Names

SQL Server - Invalid characters in parameter names

Search for "Identifiers" in your SQL Books online, and you should find:

Rules for Regular Identifiers

The rules for the format of regular identifiers depend on the database
compatibility level. This level can be set by using sp_dbcmptlevel.
When the compatibility level is 90, the following rules apply:

The first character must be one of the following:

• A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z,
  
  from A through Z, and also letter characters from other languages.
• The underscore (_), at sign (@), or number sign (#).

Certain symbols at the beginning of an identifier have special
meaning in SQL Server. A regular identifier that starts with the at
sign always denotes a local variable or parameter and cannot be used
as the name of any other type of object. An identifier that starts
with a number sign denotes a temporary table or procedure. An
identifier that starts with double number signs (##) denotes a global
temporary object. Although the number sign or double number sign
characters can be used to begin the names of other types of objects,
we do not recommend this practice.

Some Transact-SQL functions have names that start with double at signs
(@@). To avoid confusion with these functions, you should not use
names that start with @@.

Subsequent characters can include the following:

• Letters as defined in the Unicode Standard 3.2.
• Decimal numbers from either Basic Latin or other national scripts.
• The at sign, dollar sign ($), number sign, or underscore.

The identifier must not be a Transact-SQL reserved word. SQL Server
reserves both the uppercase and lowercase versions of reserved words.
Embedded spaces or special characters are not allowed. Supplementary
characters are not allowed.

Search for "delimited identifiers" in your SQL Books online, and you should find:

The body of the identifier can contain
any combination of characters in the
current code page, except the
delimiting characters themselves. For
example, delimited identifiers can
contain spaces, any characters valid
for regular identifiers, and any one
of the following characters.

tilde (~)                hyphen (-)   
exclamation point (!)    left brace ({)   
percent (%)              right brace (})   
caret (^)                apostrophe (')   
ampersand (&)            period (.)   
left parenthesis (()     backslash (\)   
right parenthesis ())    accent grave (`)

Marc

SQL get all records which contains invalid character for e-diaeresis

If you want just to SELECT the data then

SELECT *, REPLACE(CustomValue, 'A«', N'ë')
FROM [dbo].[Table] 
WHERE [dbo].[Table].CustomValue LIKE '%A«%'

If you really needs to UPDATE the data then

UPDATE [dbo].[Table]
SET CustomValue = REPLACE(CustomValue, 'A«', N'ë')
WHERE [dbo].[Table].CustomValue LIKE '%A«%'

What special characters are allowed in T-SQL column name?

from MSDN:

The first character must be one of the following:

• A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z, from A through Z, and also letter characters from other languages.
• The underscore (_), at sign (@), or number sign (#).

Subsequent characters can include the following:

• Letters as defined in the Unicode Standard 3.2.
• Decimal numbers from either Basic Latin or other national scripts.
• The at sign, dollar sign ($), number sign, or underscore.

The identifier must not be a Transact-SQL reserved word. SQL Server reserves both the uppercase and lowercase versions of reserved words.

Embedded spaces or special characters are not allowed.

Supplementary characters are not allowed.

edit

refering to NinthSense: the specs also says:

Certain symbols at the beginning of an identifier have special meaning in SQL Server. A regular identifier that starts with the at sign always denotes a local variable or parameter and cannot be used as the name of any other type of object.

and this statement can be executed without errors:

create table #t (
  #oid int ,
  äß int,
  ßdid varchar(10),
  _data varchar(10)
)

How to find invalid Char in a SQL table

I have found a solution in another users question here

I modified it slightly though. What ends up working for me is this:

ALTER FUNCTION [dbo].[RemoveNonASCII] 
(
    -- Parameters
    @nstring nvarchar(max)
)
RETURNS varchar(max)
AS
BEGIN
    -- Variables
    DECLARE @Result varchar(max) = '',@nchar nvarchar(1), @position int

    -- T-SQL statements to compute the return value
    set @position = 1
    while @position <= LEN(@nstring)
    BEGIN
        set @nchar = SUBSTRING(@nstring, @position, 1)
        if UNICODE(@nchar) between 32 and 127
            set @Result = @Result + @nchar
        set @position = @position + 1
        set @Result = REPLACE(@Result,'))','')
        set @Result = REPLACE(@Result,'?','')
    END

    -- Return the result
    RETURN @Result

END

---

Related Topics