Escaping a modulo in an nvarchar search string

Question

Escaping a modulo in an nvarchar search string

caspersql

SSCrazy

Points: 2276
More actions
September 7, 2010 at 7:55 am

#237964

I have a list of German URLs stored in a table. Sample data:
-- Create temporary test table
CREATE TABLE #GermanURLs
(SourceURL nvarchar(1000) NULL)
-- Insert test data
INSERT INTO #GermanURLs
(SourceURL)
VALUES
('http://ww2.bafin.de/database/DealingsInfo/transaktionListe.do?cmd=loadTransaktionenAction&emittentBafinId=40002104&emittentName=118000+AG&meldepflichtigerVorname=Dr.+Klaus+&meldepflichtigerName=Harisch')
,('http://ww2.bafin.de/database/DealingsInfo/transaktionListe.do?cmd=loadTransaktionenAction&emittentBafinId=40002104&emittentName=118000+AG&meldepflichtigerVorname=Dr.+Klaus+&meldepflichtigerName=Harisch')
,('http://ww2.bafin.de/database/DealingsInfo/transaktionListe.do?cmd=loadTransaktionenAction&emittentBafinId=40002104&emittentName=118000+AG&meldepflichtigerVorname=Peter+&meldepflichtigerName=W%fcnsch')
,('http://ww2.bafin.de/database/DealingsInfo/transaktionListe.do?cmd=loadTransaktionenAction&emittentBafinId=40002104&emittentName=118000+AG&meldepflichtigerVorname=Peter+&meldepflichtigerName=W%fcnsch')
,('http://ww2.bafin.de/database/DealingsInfo/transaktionListe.do?cmd=loadTransaktionenAction&emittentBafinId=40002104&emittentName=118000+AG&meldepflichtigerVorname=Peter+&meldepflichtigerName=W%fcnsch')
,('http://ww2.bafin.de/database/DealingsInfo/transaktionListe.do?cmd=loadTransaktionenAction&emittentBafinId=99500841&emittentName=4SC+AG&meldepflichtigerVorname=Dr.+Ulrich+&meldepflichtigerName=Dauer')
As you can see characters like ü are substituted with their hexadecimal codes in the URLs, so 'Wünsch' is written as 'W%fcnsch'
I'm trying to find all the instances where a substitution has taken place by searching for the % modulo character within the SourceURL column. However, despite the modulo being present in the nvarchar SourceURL field, when I perform this search:
SELECT * FROM #GermanURLs
WHERE SourceURL LIKE '%' + ''%''+ '%'
I get this error message:
Msg 402, Level 16, State 1, Line 2
The data types varchar and varchar are incompatible in the modulo operator.
Can anyone tell me how to search for the modulo operator within an nvarchar string? If its not possible due to 'incompatability', is there another way to achieve what I'm trying to do?
September 7, 2010 at 8:07 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

caspersql SSCrazy Points: 2276 More actions · Answer 1

I should step away from my desk more often - solutions always seem to pop into my head when I'm doing nothing

After an hour of looking for a solution to this online I just thought of a way to get around this when I went to the loo 🙂 SQL doesn't seem to mind the 'incompatability' if you search a string that has replaced the modulo with another character as follows (uses data from the test table mentioned above):

SELECT * FROM #GermanURLs

WHERE REPLACE(SourceURL, '%', 'THISONEHASAMODULO') LIKE '%THISONEHASAMODULO%'

caspersql SSCrazy Points: 2276 More actions · Answer 2

stewartc - many thanks, that works nicely too 🙂 I wasn't aware of the escape command previously.

WayneS SSC Guru Points: 95409 More actions · Answer 3

You can also go:

SELECT *

FROM #GermanURLs

WHERE SourceURL LIKE '%[%]%'

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 4

Another method

select * from #GermanURLs

where charindex('%',SourceURL)>0

Madhivanan

Failing to plan is Planning to fail