How can I test a field for a specific character in SQL

[stanlyn]

How do I determine if a SQL Server 2012 field contains a specific character or string without using fulltext commands using a select?

Thanks, Stanley

 

[bborissov]

Check CHARINDEX() and/or PATINDEX() functions in BOL.

Borislav Borissov
VFP9 SP2, SQL Server

 

[gmmastros]

If you want to test for all numerics in a string column....

Select *
From   YourTable
Where  IsNumeric(YourColumnName + 'e0') = 0

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[stanlyn]

George,

> IsNumeric(YourColumnName + 'e0')

Why the 'e0'? How is that relative to the column name?

An example relating to my original question is:

1. How do I test if field contains a 'Z' or 'a9a' (anywhere in the field)
2. test if field contains a '9' (anywhere in the field)

Thanks, Stanley

 

[stanlyn]

Borislav,

Thanks, the charindex() was what I was looking for.

Thanks, Stanley