Menu
I am working with SQL Server 2005.
I need to find out only those rows for which there is a special character in “Body” column. In the following scenario, the result should be only the row with TemplateID = 2. How do we write the query for this?
CREATE TABLE #Template (TemplateID INT, Body VARCHAR(100))
INSERT INTO #Template (TemplateID,Body) VALUES (1,'abcd 1234')
INSERT INTO #Template (TemplateID,Body) VALUES (2,'#^!@')
Anything other than the following is a special character for this scenario
1) Alphabtes
2) Digits
3) Space
787
If special characters are number( 0-9 ) and these characters ( '" ) than you could write select F_name from yourtable WHERE F_name LIKE '%[0-9''"]% .
Use braces to escape a string of characters or symbols. Everything within a set of braces in considered part of the escape sequence. When you use braces to escape a single character, the escaped character becomes a separate token in the query. Use the backslash character to escape a single character or symbol.
The SUBSTRING() function extracts some characters from a string.
SELECT
TemplateID,
Body
FROM
#Template
WHERE
Body LIKE '%[^0-9a-zA-Z ]%'
The stuff between the brackets says numbers (0-9), lowercase alphas (a-z), uppercase alphas (A-Z) and the space. The "^" makes that a "NOT" one of these things. Note that this is different though than NOT LIKE '%[0-9a-zA-Z ]%'
101
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
