Prev: Batch File ???
Next: return null for the column column_default in the view sys.informat
From: mike.kolling on 3 Aug 2010 11:14
Hi

We have an unknown number of records which contain a Unicode (65533)
character in a text address field.

Ie a "�"

We need to removed these blobs.

For a specific row I have done this SQL Select...

SELECT UNICODE(SUBSTRING(Address1_Line1,7,1))
FROM [DATABASE].[dbo].[Contact]
where ContactId in ( 'A9DCDB9D-06C6-DE11-ADDF-00237DEC9650')

This returned 65533 and 63 for ASCII(...)

So now, how do I find any other records which may contain this
character?

I have tried the following with no joy...

where
patindex(char(63), COLUMN) > 0

and

where
patindex(unicode(65533), COLUMN) > 0

and

Where
COLUMN like '%�%'
From: Eric Isaacs on 3 Aug 2010 15:33
Based on your identification of the character, here is how you could
detect it and here is how you can also replace it with a space...

DECLARE @BadChar AS NCHAR
SET @BadChar = NCHAR(65533)
SELECT @BadChar

SELECT * FROM dbo.contact
WHERE columnname LIKE ('%' + @BadChar + '%'


BEGIN TRANSACTION

UPDATE dbo.contact
SET ColumnName = REPLACE(ColumnName, @BadChar, ' ')
WHERE columnname LIKE ('%' + @BadChar + '%'

--ROLLBACK TRANSACTION --you can uncomment this line and comment the
next line for testing.
COMMIT TRANSACTION


-Eric Isaacs
From: Erland Sommarskog on 3 Aug 2010 15:47
mike.kolling(a)gmail.com (mike.kolling(a)gmail.com) writes:
> We have an unknown number of records which contain a Unicode (65533)
> character in a text address field.

The character is "Replacement character" and is used when a there is an
illegal code sequence; I've seen at web pages at times.

> We need to removed these blobs.
>
> For a specific row I have done this SQL Select...
>
> SELECT UNICODE(SUBSTRING(Address1_Line1,7,1))
> FROM [DATABASE].[dbo].[Contact]
> where ContactId in ( 'A9DCDB9D-06C6-DE11-ADDF-00237DEC9650')
>
> This returned 65533 and 63 for ASCII(...)
>
> So now, how do I find any other records which may contain this
> character?
>
> I have tried the following with no joy...

By fooling around I found that it works if you force a binary collation:

create table #B (id int NOT NULL, a nvarchar(23) NOT NULL)
INSERT #B (id, a)
SELECT 1, N'A' + nchar (65533) + N'N'
UNION ALL
SELECT 2, nchar (65533)
UNION ALL
SELECT 3, 'Lalla'
UNION ALL
SELECT 4, N'Putte' + nchar (65533)
go
select * from #B
SELECT * FROM #B
where charindex(nchar(65533) COLLATE Latin1_General_BIN2, a) > 0
update #B
set a = replace(a, nchar(65533) COLLATE Latin1_General_BIN2, '')
select * from #B
go
DROP TABLE #B





--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Mike on 4 Aug 2010 09:21
On 3 Aug, 20:47, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> mike.koll...(a)gmail.com (mike.koll...(a)gmail.com) writes:
> > We have an unknown  number of records which contain a Unicode (65533)
> > character in a text address field.
>
> The character is "Replacement character" and is used when a there is an
> illegal code sequence; I've seen at web pages at times.
>
> > We need to removed these blobs.
>
> > For a specific row I have done this SQL Select...
>
> > SELECT UNICODE(SUBSTRING(Address1_Line1,7,1))
> > FROM [DATABASE].[dbo].[Contact]
> >   where ContactId in ( 'A9DCDB9D-06C6-DE11-ADDF-00237DEC9650')
>
> > This returned 65533 and 63 for ASCII(...)
>
> > So now, how do I find any other records which may contain this
> > character?
>
> > I have tried the following with no joy...
>
> By fooling around I found that it works if you force a binary collation:
>
> create table #B (id int NOT NULL, a nvarchar(23) NOT NULL)
> INSERT #B (id, a)
>    SELECT 1, N'A' + nchar (65533) + N'N'
>    UNION ALL
>    SELECT 2, nchar (65533)
>    UNION ALL
>    SELECT 3, 'Lalla'
>    UNION ALL
>    SELECT 4, N'Putte' + nchar (65533)
> go
> select * from #B
> SELECT * FROM #B
> where charindex(nchar(65533) COLLATE Latin1_General_BIN2, a) > 0
> update #B
> set    a = replace(a, nchar(65533) COLLATE Latin1_General_BIN2, '')
> select * from #B
> go
> DROP TABLE #B
>
> --
> Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks for your help guys, both worked, I also found this one which
works as well...


select *
from
dbo.FilteredContact
where
Address1_Line3 != cast(Address1_Line3 as varchar(1000))
 | 
Pages: 1
Prev: Batch File ???
Next: return null for the column column_default in the view sys.informat