How to determine if a hyphen (-) exists inside a column

You didn't mention why the code you provided doesn't work. CONTAINS is for use with SQL Server's full text search feature. If you're not using this, then you need to use a LIKE clause with wildcards:

CASE WHEN SUBSTRING(al.ALT_ADDRESS,1,1) IN('1','5','7') AND al.NEW_ADDRESS LIKE '%-%' 
THEN CONCAT(al.ALT_ADDRESS,al.NEW_ADDRESS)

Even if you are using full text search, the matching behavior with dashes can be unexpected and Microsoft recommends using LIKE instead.

---

You might double check that the character is really a hyphen, something like this should work:

SELECT 
    ASCII('-') as RealHypen,
    ASCII(SUBSTRING(NEW_ADDRESS, 1, 1))
FROM YourTable
WHERE ALT_ADDRESS = '2754 Churchill Circle';

---

As an aside, the LIKE expression above is not able to make use of the seeking abilities of a b-tree index in SQL Server, so performance may suffer on large tables if a large scanning operation is required. The best way to mitigate that (should it apply to you or others in a similar situation) depends heavily on context, but in general the main alternatives are:

• Indexing a computed column that evaluates the search condition
• Using triggers to persist the search result in advance
• Use an external tool that's more suited to searching text (Elasticsearch is popular)
• Use n-grams (typically for substrings of 3 characters or more)
• Use Full Text Search (this won't help with wildcards per se, or this specific case, but can work for word-based searching).

If you are interested in exploring the performance aspect, please ask a follow up question.

An alternative approach to the existing answer is to use the CHARINDEX() function which returns the position of the specified string if exists, otherwise 0.

select charindex('-','kevin-')

Will return 6, because the hyphen is located in the sixth position of the string, compared with

select charindex('-','kevin')

returns 0 because the '-' is not present in the string.

---

Per John Eisbrener's suggestion, PATINDEX is also an option, and it allows for wildcards - which may prove beneficial in certain circumstances.