PatIndex Pattern

I am attempting to use PatIndex to find characters outside of the range of
character codes 32 - 126, or in other words, find all characters in the
ranges of 0 - 31 and 127 - 255. I have written the following so far:
DECLARE @.str varchar(1000)
SET @.str = '\[%]ZNORMAL 123? 0?å
0?å
jûwH1øwÿ..0 j)ß©0 '
SELECT
PATINDEX('%[^ !"#$%&()*+,-./0123456789:;<=>?@.ABCDEFGHIJKLMNOPQRSTUVWXYZ\
`abcdefghijklmnopqrstuvwxyz]%', @.str)
How would I include the following characters in the pattern search: '^[]%
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1I have worked on this problem a bit in the past, and never found a way to
escape all of those characters properly in a PATINDEX pattern. The only
solution I came up with--which is somewhat suboptimal--is to write a UDF
that uses LIKE and evaluates each character in the string. LIKE has an
optional ESCAPE argument that lets this solution work:
--
CREATE FUNCTION EscapedPATINDEX
(
@.Pattern VARCHAR(200),
@.String VARCHAR(8000),
@.Escape CHAR(1)
)
RETURNS INT
AS
BEGIN
DECLARE @.return INT
SELECT @.return = MIN(Number)
FROM Numbers
WHERE
number >= 1
AND number <= DATALENGTH(@.String)
AND SUBSTRING(@.String, number, 1) LIKE @.Pattern ESCAPE @.Escape
RETURN (@.Return)
END
GO
--
This UDF allows you to do, e.g.:
--
SELECT dbo.EscapedPATINDEX('[^ \^]', '^^^c^^^', '\')
--Returns 4
--
Note that this UDF requires a table of numbers. See the following link
if you don't already have one:
http://sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspx
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"cbrichards" <u3288@.uwe> wrote in message news:5fcd316e41596@.uwe...
>I am attempting to use PatIndex to find characters outside of the range of
> character codes 32 - 126, or in other words, find all characters in the
> ranges of 0 - 31 and 127 - 255. I have written the following so far:
> DECLARE @.str varchar(1000)
> SET @.str = '\[%]ZNORMAL 123? 0?å
> 0?å
> jûwH1øwÿ..0 j)ß©0 '
> SELECT
> PATINDEX('%[^ !"#$%&()*+,-./0123456789:;<=>?@.ABCDEFGHIJKLMNOPQRSTUVWXYZ\
> `abcdefghijklmnopqrstuvwxyz]%', @.str)
> How would I include the following characters in the pattern search: '^[]%
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Thanks Adam. Works great!
Adam Machanic wrote:
>I have worked on this problem a bit in the past, and never found a way to
>escape all of those characters properly in a PATINDEX pattern. The only
>solution I came up with--which is somewhat suboptimal--is to write a UDF
>that uses LIKE and evaluates each character in the string. LIKE has an
>optional ESCAPE argument that lets this solution work:
>--
>CREATE FUNCTION EscapedPATINDEX
>(
> @.Pattern VARCHAR(200),
> @.String VARCHAR(8000),
> @.Escape CHAR(1)
>)
>RETURNS INT
>AS
>BEGIN
> DECLARE @.return INT
> SELECT @.return = MIN(Number)
> FROM Numbers
> WHERE
> number >= 1
> AND number <= DATALENGTH(@.String)
> AND SUBSTRING(@.String, number, 1) LIKE @.Pattern ESCAPE @.Escape
> RETURN (@.Return)
>END
>GO
>--
> This UDF allows you to do, e.g.:
>--
>SELECT dbo.EscapedPATINDEX('[^ \^]', '^^^c^^^', '\')
>--Returns 4
>--
> Note that this UDF requires a table of numbers. See the following link
>if you don't already have one:
>http://sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspx
>>I am attempting to use PatIndex to find characters outside of the range of
>> character codes 32 - 126, or in other words, find all characters in the
>[quoted text clipped - 10 lines]
>> How would I include the following characters in the pattern search: '^[]%
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1