Searching for a carriage return in a text field

levinll · March 10th, 2004, 11:46 AM

Somehow a user was able to enter a CR/LF into a text field in one of my tables. I'd like to be able to search the rest of the records to see if this has happened before, but I'm not sure of the syntax I'd have to use. Can anyone enlighten me ?

Also does anyone have any idea on how to prevent a CR/LF from being inserted into a database ? I'm using VB as a front end/SQL Server as the back end.

Jeff Mason · March 10th, 2004, 01:02 PM

With SQL Server you can use the CHARINDEX and CHAR functions:

Code:

SELECT * FROM yourtable
WHERE CHARINDEX(CHAR(13)+CHAR(10),yourcolumn)>0;

will find strings which contain CR/LF.

You should prevent the inclusion of these 'bad' characters in your client code. If your requirements dictate that a string may not contain CR/LF, then this is an enforcement of a business rule which is best handled at the client.

If you use a stored procedure to do your updates, you could also search any input string parameter using code similar to the CHARINDEX snippet above and SUBSTRING out the CR/LF. Languages like T/SQL aren't very efficient doing character string manipulation, so this is better handled at the client, IMO.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

rgerald · March 10th, 2004, 03:53 PM

Dear levinll:

This also works:

Code:

SELECT * FROM TableToSearch WHERE ColumnToSearch LIKE('%'+char(13)+char(10)+'%')

Rand

rgerald · March 10th, 2004, 04:00 PM

Here's the VB code to get rid of CR/LF in a string:

Code:

Replace(StringToSearch, Chr(13)&Chr(10), "")

Rand