Finding Special Characters with a SELECT Statement

[Fenderson]

Hey Swynkers,

I've got a tricky one. I'm trying to do a SELECT statement to return all of our email fields that include the Newline character, or Char(13). I've tried all of the following with no result:

WHERE Email like "%Char(13)%"
WHERE charindex(Char(13), @Email) > 0
WHERE patindex(@NewLine, @Email) > 0

Any suggestions?

Thanks,

Fenderson

[Kenneth Wilhelmsson]

Hi.

Try:
where charindex(convert(varchar(3),char(13)), Email) > 0

/Kenneth

------------
Fenderson at 3/12/99 4:10:37 PM


Hey Swynkers,

I've got a tricky one. I'm trying to do a SELECT statement to return all of our email fields that include the Newline character, or Char(13). I've tried all of the following with no result:

WHERE Email like "%Char(13)%"
WHERE charindex(Char(13), @Email) > 0
WHERE patindex(@NewLine, @Email) > 0

Any suggestions?

Thanks,

Fenderson

[Fenderson]

It worked Great!

Thanks for the help. You're a life saver!

Fenderson

BTW - How did you know that? Is there a listing somewhere of SQL system constants?

------------
Kenneth Wilhelmsson at 3/12/99 5:01:57 PM

Hi.

Try:
where charindex(convert(varchar(3),char(13)), Email) > 0

/Kenneth

------------
Fenderson at 3/12/99 4:10:37 PM


Hey Swynkers,

I've got a tricky one. I'm trying to do a SELECT statement to return all of our email fields that include the Newline character, or Char(13). I've tried all of the following with no result:

WHERE Email like "%Char(13)%"
WHERE charindex(Char(13), @Email) > 0
WHERE patindex(@NewLine, @Email) > 0

Any suggestions?

Thanks,

Fenderson

[Kenneth Wilhelmsson]

All functions is described in BOL (don't leave home without it ;-)

Your version: WHERE charindex(Char(13), @Email) > 0
didn't work because char(13) returns an int and charindex expects a string expression as first parameter, so you need to convert the datatype returned.

/Kenneth

------------
Fenderson at 3/12/99 5:11:51 PM


It worked Great!

Thanks for the help. You're a life saver!

Fenderson

BTW - How did you know that? Is there a listing somewhere of SQL system constants?

------------
Kenneth Wilhelmsson at 3/12/99 5:01:57 PM

Hi.

Try:
where charindex(convert(varchar(3),char(13)), Email) > 0

/Kenneth

------------
Fenderson at 3/12/99 4:10:37 PM


Hey Swynkers,

I've got a tricky one. I'm trying to do a SELECT statement to return all of our email fields that include the Newline character, or Char(13). I've tried all of the following with no result:

WHERE Email like "%Char(13)%"
WHERE charindex(Char(13), @Email) > 0
WHERE patindex(@NewLine, @Email) > 0

Any suggestions?

Thanks,

Fenderson