June 21, 2011 at 7:30 pm
My environment: SQL Server 2008 and Windows Server 2008. Collation for this database is set to SQL_Latin1_General_CP1_CI_AS.
A developer reported an issue with one of his application.
There is a column for email addresses varchar(100).
len(ltrim(rtrim(email_address))) returns 1 for this particular row/column, there are no visible characters there.
I know it can't be null because the len is 1 or am i wrong here?.
so if I run a query:
select ssn, name, email_address, ASCII(ltrim(rtrim(email_address))) as ASCII_CODE from tblTest where right(ssn,4) = '1234' and name = 'Some Name'
the row is returned with nothing visible in the email_address column and 0 (zero) in ASCII_CODE column.
if I change the where clause and add "and email_address is null", it brings no row.
If i check the ASCII value for the column, it returns 0 (zero) which is null in ASCII code table.
Question 1: how do I find out the true value of this invisible character. If it really is a NULL, why the row is not returned?
Question 2: how do I prevent white spaces in future. I know I can use patindex or charindex with replace function to fix the issue or put a CONSTRAINT on that column, are there any better ways.
Any help would be greatly appreciated. Thank you.
June 21, 2011 at 9:17 pm
T-SQL does not use the ASCII null character to represent a NULL string. As far as SQL is concerned, there is no difference between ASCII 0 and any other ASCII character. You can easily see this by running the following query.
SELECT CHAR(0)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 22, 2011 at 1:11 pm
For Q2 I would recommend you prevent bad characters as far away from the table as possible as edge-cases like this can wreak havoc on your system, e.g. strip them out in the application layer code or inside a stored procedure. I would also hedge my bets to ensure only quality data can be stored by adding a CHECK CONSTRAINT to the table.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 23, 2011 at 1:43 pm
Perhaps a user entered some trash for the email address in the web form. When I exec the following, I see several character values that are invisible in a grid or as text in the Result window, like char(9) TAB and char(10) CR. As a start, consider updating the email column to NULL for any value that has a length <= 3.
set nocount on;
declare @i int; select @i = 1;
while @i <= 255
begin
select @i ascii_code, char(@i) char_value
select @i = @i + 1;
end;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 27, 2011 at 7:23 am
Eric M Russell (6/23/2011)
Perhaps a user entered some trash for the email address in the web form. When I exec the following, I see several character values that are invisible in a grid or as text in the Result window, like char(9) TAB and char(10) CR. As a start, consider updating the email column to NULL for any value that has a length <= 3.
set nocount on;
declare @i int; select @i = 1;
while @i <= 255
begin
select @i ascii_code, char(@i) char_value
select @i = @i + 1;
end;
Or better yet, update based on this sort of query -
SELECT
CASE WHEN validate.atindex = 0
THEN NULL
ELSE RIGHT(validate.head, Patindex('% %', Reverse(validate.head) + ' ') - 1) +
LEFT(validate.tail + ' ', Patindex('% %', validate.tail + ' ')) END email
FROM your_table a
LEFT OUTER JOIN (SELECT
thekey,
RIGHT(sq.email, sq.[len] - sq.atindex) AS tail,
LEFT(sq.email, sq.atindex) AS head, sq.atindex
FROM (SELECT
guid AS thekey, --use whatever primary key you have
Patindex('%[A-Z0-9]@[A-Z0-9]%', email + ' ') AS atindex,
Len(email + '|') - 1 AS [len], email
FROM your_table) AS sq ) AS validate ON c.guid = validate.thekey --Join on your primary key
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply