February 28, 2020 at 11:21 pm
I have a table with a varchar field. It contains a number of characters causing us issues, and I believe they fall between hex range 0x80 and 0x9F (I believe they are the 32 characters in the cp1252 character set that have different mappings from ISO-8859-1). I'm trying to identify them using the below query, but it's not working (returning basically every value with a patindex of 1). Any help would be greatly appreciated!
select top 2000
A.*,
patindex('%[\x80-\x9F]%' COLLATE Latin1_General_BIN, MyField) AS PatIndexPosition
from MyTable A
where patindex('%[\x80-\x9F]%' COLLATE Latin1_General_BIN, MyField) > 0
February 29, 2020 at 10:45 pm
Embed your search argument in a variable, and use that in the function:
-- sample data
CREATE TABLE #Testr(id int not null primary key, MyField varchar(128))
GO
INSERT #Testr(id, MyField)
SELECT object_id,
-- using a modulo on the checksum of a NEWID() value to decorate ~10%
-- of the sample rows with a target character
name + case when abs(checksum(newid())) % 10 = 0 then char(130) else '' end
FROM sys.objects
GO
-- embed the search args in a variable
DECLARE @srch varchar(100) = '%[' + char(0x80) + '-' + char(0x9f) + ']%'
-- use the variable to search
SELECT id, a.MyField, patindex(@srch COLLATE Latin1_General_BIN, MyField) AS PatIndexPosition
FROM #Testr A
WHERE patindex(@srch COLLATE Latin1_General_BIN, MyField) > 0
Eddie Wuerch
MCM: SQL
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy