SP Escape Character???

SP Escape Character???

Post by Gerar » Thu, 04 Dec 2003 18:28:26



I am migrating SQL from VB to T-SQL in a SP for SQL
Server.  Everything is fine, but I can't make, or use an
Escape Character ('\', '%' for example.)  I am building
strings in the SP and NEED a Escape.  I follow the
documentation and it does not work.  Any Thoughts??

P.S.
Thank you Linda for your DISTINCT fix, that did the trick!!

Here's an example SP:(more info below SP)


AS


tblMortgageAudit.AudType,
tblMortgageAudit.AudCompID, tblMortgageAudit.AudDate,
tblMortgageAudit.AudTime, tblMortgageAudit.AudHomeid,
tblMortgageAudit.AudName,
tblMortgageAudit.AudMortgageReferenceNumber,
tblMortgageAudit.AudGMS_Tracking,
tblMortgageAudit.AudAmount,
tblMortgageAudit.AudCheckNumber,
tblMortgageAudit.AudReturnReasonCode,
tblMortgageAudit.AudReturnEffectiveDate,
tblMortgageAudit.AudRemarks,
tblMortgageAudit.ClearedDate, tblMortgageAudit.ChangeFlag'



    BEGIN

(tblMortgageAudit.AudCompID Not Like "NC_" AND
tblMortgageAudit.AudCompID Not Like "0N_" AND
tblMortgageAudit.ChangeFlag Like -1)'
    END
ELSE
    BEGIN

(tblMortgageAudit.AudCompID Not Like "NC_" AND
tblMortgageAudit.ChangeFlag Like -1)'
    END

[ID]'


GO

Notice the LIKE clause needs the search term in '...'
format, well T-SQL uses '...' to designate a string, and I
am building a string there, therefore an Escape or
Terminate character is needed or it sees the string as
ending and starting a new one.  And T-SQL does not
see "..." as a substituion like VB would.  Any help is
greatly appreciated.

 
 
 

SP Escape Character???

Post by Aaron Bertrand - MV » Thu, 04 Dec 2003 18:45:07


Quote:> Notice the LIKE clause needs the search term in '...'
> format, well T-SQL uses '...' to designate a string, and I
> am building a string there, therefore an Escape or
> Terminate character is needed or it sees the string as
> ending and starting a new one.  And T-SQL does not
> see "..." as a substituion like VB would.  Any help is
> greatly appreciated.

Double up ' to escape single quotes.  So, to find rows where the column
contains

    Bob's bait n' tackle

Use:

    WHERE column LIKE '%Bob''s bait n'' tackle%'

You can use [] square brackets to escape special wildcard characters such as
%, _ and [

So, if you are looking literally for this exact string:

    [Bob_bait%tackle]

Use:

    WHERE column LIKE '%[[]Bob[_]bait[%]tackle]%'

Or you can avoid the wildcard problems by using:

    WHERE CHARINDEX('[Bob_bait%tackle]', column) > 0

(If the column is TEXT/NTEXT, you will need to use PATINDEX instead.)

Try this demo:

SET NOCOUNT ON

CREATE TABLE blat
(
 splunge VARCHAR(255)
)
GO

INSERT blat(splunge)
 VALUES('I like to visit Bob''s bait n'' tackle often.')

INSERT blat(splunge)
 VALUES('I like to visit [Bob_bait%tackle] often.')

INSERT blat(splunge)
 VALUES('I can''t stand Bob.')
GO

SELECT splunge FROM blat

SELECT splunge FROM blat
 WHERE splunge LIKE '%Bob''s bait n'' tackle%'

SELECT splunge FROM blat
 WHERE CHARINDEX('Bob''s bait', splunge) > 0

SELECT splunge FROM blat
 WHERE splunge LIKE '%[[]Bob[_]bait[%]tackle]%'

SELECT splunge FROM blat
 WHERE CHARINDEX('[Bob_bait%tackle]', splunge) > 0
GO

DROP TABLE blat
GO

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/