December 28, 2007 at 6:19 am
Hello
We've hit a problem where we can't select some records through our sales application to change the address. I've found it is due to characters of 0x00 in the record instead of 0x. The way the application code is written it keeps saying the record has already been changed since loading.
e.g. The code below breaks the field city down into individual characters converted to varbinary as its the only way I've found I can display the problem:
select city,
cast(substring(city,1,1) as varbinary),
cast(substring(city,2,1) as varbinary),
cast(substring(city,3,1) as varbinary),
cast(substring(city,4,1) as varbinary),
cast(substring(city,5,1) as varbinary),
cast(substring(city,6,1) as varbinary),
cast(substring(city,7,1) as varbinary),
cast(substring(city,8,1) as varbinary),
cast(substring(city,9,1) as varbinary),
cast(substring(city,10,1) as varbinary),
cast(substring(city,11,1) as varbinary),
cast(substring(city,12,1) as varbinary),
cast(substring(city,13,1) as varbinary),
cast(substring(city,14,1) as varbinary),
cast(substring(city,15,1) as varbinary),
cast(substring(city,16,1) as varbinary),
cast(substring(city,17,1) as varbinary),
cast(substring(city,18,1) as varbinary),
cast(substring(city,19,1) as varbinary),
cast(substring(city,20,1) as varbinary),
cast(substring(city,21,1) as varbinary),
cast(substring(city,22,1) as varbinary),
cast(substring(city,23,1) as varbinary),
cast(substring(city,24,1) as varbinary),
cast(substring(city,25,1) as varbinary),
cast(substring(city,26,1) as varbinary),
cast(substring(city,27,1) as varbinary),
cast(substring(city,28,1) as varbinary),
cast(substring(city,29,1) as varbinary),
cast(substring(city,30,1) as varbinary)
from T_ADDRESS
where customer_no >= '380180'
and customer_no <= '380184'
and address_type = '3'
Returns:
Cambridgeshire 0x430x610x6D0x620x720x690x640x670x650x730x680x690x720x650x200x200x200x200x200x200x000x000x000x000x000x000x000x000x000x00
Stockport0x530x740x6F0x630x6B0x700x6F0x720x740x0x0x0x0x0x0x0x0x0x0x0x0x0x0x0x0x0x0x0x0x
Nantwich0x4E0x610x6E0x740x770x690x630x680x0x0x0x0x0x0x0x0x0x0x0x0x0x0x0x0x0x0x0x0x0x
Above Cambridgeshire is the broken record I want to fix, the other two are there for examples of correct records.
What I'm interested in doing is searching on the last digit. If it is 0x00, return the record. This sounds easy, but I haven't found a way of searching on this varbinary value 0x00. The script below returns both 0x and 0x00
select address_no, customer_no,
cast(substring(city,30,1)as varbinary) as checker,
from T_ADDRESS
where customer_no >= '380180'
and customer_no <= '380184'
and cast(substring(city,30,1)as varbinary) = 0x00
If I save this output as a file and reimport the binary value as nvarchar, I can search for the 0x00 values then, but there must be a better way of doing this.
Anyone any suggestions
Cheers
Wayne
To save you creating a table and inserting the data, I've done it below. Hopefully you can copy and paste.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.T_ADDRESS
(
city varchar(30) NULL
) ON [PRIMARY]
GO
COMMIT
insert into T_ADDRESS
select left(convert(char,0x43),1)
+left(convert(char,0x61),1)
+left(convert(char,0x6D),1)
+left(convert(char,0x62),1)
+left(convert(char,0x72),1)
+left(convert(char,0x69),1)
+left(convert(char,0x64),1)
+left(convert(char,0x67),1)
+left(convert(char,0x65),1)
+left(convert(char,0x73),1)
+left(convert(char,0x68),1)
+left(convert(char,0x69),1)
+left(convert(char,0x72),1)
+left(convert(char,0x65),1)
+left(convert(char,0x20),1)
+left(convert(char,0x20),1)
+left(convert(char,0x20),1)
+left(convert(char,0x20),1)
+left(convert(char,0x20),1)
+left(convert(char,0x20),1)
+left(convert(char,0x00),1)
+left(convert(char,0x00),1)
+left(convert(char,0x00),1)
+left(convert(char,0x00),1)
+left(convert(char,0x00),1)
+left(convert(char,0x00),1)
+left(convert(char,0x00),1)
+left(convert(char,0x00),1)
+left(convert(char,0x00),1)
+left(convert(char,0x00),1)
insert into T_ADDRESS
select left(convert(char,0x53),1)
+left(convert(char,0x74),1)
+left(convert(char,0x6F),1)
+left(convert(char,0x63),1)
+left(convert(char,0x6B),1)
+left(convert(char,0x70),1)
+left(convert(char,0x6F),1)
+left(convert(char,0x72),1)
+left(convert(char,0x74),1)
insert into T_ADDRESS
select left(convert(char,0x4E),1)
+left(convert(char,0x61),1)
+left(convert(char,0x6E),1)
+left(convert(char,0x74),1)
+left(convert(char,0x77),1)
+left(convert(char,0x69),1)
+left(convert(char,0x63),1)
+left(convert(char,0x68),1)
select * from T_ADDRESS
December 28, 2007 at 7:12 am
did you try using DATALENGTH function to limit the length to 2? (Writeup from BOL is below)
Toni
DATALENGTH
Returns the number of bytes used to represent any expression.
Syntax
DATALENGTH ( expression )
Arguments
expression
Is an expression of any type.
Return Types
int
Remarks
DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.
The DATALENGTH of NULL is NULL.
December 28, 2007 at 7:22 am
TO expand on my prior post. The code below would return just 0x from the table
Toni /* added a second example */
declare @temp table (id int identity, testdata varbinary(5))
insert into @temp(testdata)
select 0x72 Union
select 0x20 Union
select 0x00 union
select 0x
select testdata from @temp
where datalength(testdata) = 0
-- another variation (run it separately
declare @temp2 table (id int identity, testdata varbinary(2))
insert into @temp2(testdata)
select convert(varbinary,0x72) Union all
select convert(varbinary,0x00) Union all
select convert(varbinary,0x)
select testdata from @temp2
where testdata = 0x and datalength(testdata) = 0
December 28, 2007 at 7:39 am
Hi Tony
it wasn't 0x I was interested in, it was 0x00, but your previous suggestion worked fine:
select *,DATALENGTH(cast(substring(city,30,1) as varbinary))
from t_address
returns 1 against the record where there is something as the 30th character:
Cambridgeshire 1
Stockport0
Nantwich0
I've just tried my query and it's returned the other handful of offending records:
select address_no, customer_no,
cast(substring(city,30,1)as varbinary) as city,
DATALENGTH(cast(substring(city,30,1) as varbinary)) as datal
from T_ADDRESS
where
DATALENGTH(cast(substring(city,30,1) as varbinary)) != 0
and cast(substring(city,30,1)as varbinary) = 0x00
Thanks for your help
Wayne
December 28, 2007 at 7:47 am
Glad to be able to help Wayne. At least I got the concept right 😉
oh and it is Toni (as in Toni Marie) not Tony (as in you know...)
December 28, 2007 at 7:51 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply