CODEWITHSUNDEEP
sql-servert-sql
Kaishu
Kaishu

Reputation: 301

SQL Query returns nothing if string contains '&'

declare @vchrText varchar(max)
set @vchrText = 'AB&CDC'
 WHILE PATINDEX('%[^a-zA-Z0-9]%', @vchrText) > 0 
 Begin
                SET @vchrText = STUFF(@vchrText,

                                      PATINDEX('%[^a-zA-Z0-9&]%',

                                               @vchrText), 1, '') 
End
print @vchrText

While passing @vchrText with '&' in it returns nothing if it is not in then it returns valid result, why.?? What above query does actually..?

Upvotes: 0

Views: 194

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

The problem with your script lies in the pattern of PATINDEX used in STUFF:

This expression:

PATINDEX('%[^a-zA-Z0-9&]%', @vchrText)

contains &. Hence, PATINDEX returns 0 since @vchrText contains & character. STUFF returns NULL when the start index passed to it is 0. So, you finally receive NULL as output when @vchrText = 'AB&CDC'.

The pattern of PATINDEX used in the WHILE predicate does not contain &. Thus, it correctly detects any non-alphanumeric characters contained in @vchrText. If @vchrText contains only alphanumeric characters, then 0 is returned.

Bottom line: to fix your script simply fix the pattern of PATINDEX used in STUFF, use '%[^a-zA-Z0-9]%' in place of '%[^a-zA-Z0-9&]%'.

Upvotes: 2

Related Questions