-2

How do I select rows where a varchar column contains any characters that SQL Server considers a symbol? The symbol can be anywhere in the string.

Is there a SQL Server equivalent of the C# char.IsSymbol() function?

Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
iefpw
  • 6,816
  • 15
  • 55
  • 79

2 Answers2

5

Assume for the moment that your definition of "symbol" is any non-alphanumeric character.

You need to use SQL Server's LIKE functionality.

This will find rows containing non-alphanumerics at the start, end, and anywhere, respectively. 

Select * from MyTable where MyColumn like '[^a-zA-Z0-9]%'  -- Starts With

or

Select * from MyTable where MyColumn like '%[^a-zA-Z0-9]' -- Ends With

or

Select * from MyTable where MyColumn like '%[^a-zA-Z0-9]%' -- contains

The performance of the last one is exceptionally bad.

References

Community
  • 1
  • 1
Brian Webster
  • 30,033
  • 48
  • 152
  • 225
  • It will search all rows which has 'car', even there is no any special char(symbol) – andy Mar 12 '13 at 04:54
  • Yes I know `like` but I want to find all the symbols in SQL Server. I want list of symbols that SQL Server considers symbols. I want any and all symbols. List of symbols. I don't know what symbol I will be looking for, but I want all the symbols. If a string has a symbol (any symbol) I want the sql query to pull that row. – iefpw Mar 12 '13 at 04:54
  • It depends entirely how you define Symbol. Symbol can mean anything. He can work with this – Brian Webster Mar 12 '13 at 04:59
0

C#
U can use Indexof() to search for first occurence of symbol car and compare with 0 to check whether it is present at start of column value.

U can use LastIndexof() to search for last occurence of symbol car and compare with inputstring.Length-3 to check wheteher car is present at end. Here inputstring is the column value.

SQL Server
Use CHARINDEX() function to find position for occurence of symbol car

Select * from Table
where CHARINDEX('car',column_name)=1 or CHARINDEX('car',column_name) = LEN(column_name)-3


Note : The starting position returned by CHARINDEX() is 1-based, not 0-based.

Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133