Patindex to filter table 1

[johnpienaar]

Hi
I've got a huge (couple of million rows) table with a field called 'Main_Name' which is essentially the surname(last name) field- what I would like to do is display all the rows where Main_Name does not have any alphabetic characters eg where the field is equal to '^54' or '.,.;'
i'm a bit of a newbie, but i think you can use patindex for this, but not having any luck with it.
help would be greatly appreciated.
thanks

 

[dbomrrsm]

Create this function

CREATE FUNCTION [dbo].[IsAlpha]
(
@Text varchar(255)
)
RETURNS BIT
AS


BEGIN
DECLARE @Letters VARCHAR(255)
DECLARE @Index INT
DECLARE @Max INT
DECLARE @Pass BIT
SET @Letters = ' abcdefghijklmnopqrstuvwxyz'
SET @Max = LEN(@Text)
SET @Index = 0
SET @Pass = 1
WHILE @Index < @Max AND @Pass = 1
BEGIN
SET @Index = @Index + 1
IF NOT @Letters LIKE '%' + SUBSTRING(@Text, @Index, 1) + '%' SET @Pass = 0
END
RETURN(@Pass)
END

--Examples
--SELECT dbo.IsAlpha ('gffdesddgg') Returns 1 - True
--SELECT dbo.IsAlpha ('123gffdes)ddgg') Returns 0 - False

Then do

SELECT Myfield1,Myfield2 etc
FROM MYTABLE
WHERE dbo.IsAlpha(Myfield2) = 0


If the field being interogated contains only alpha charachters it will return 1 if it contains anything else it will return 0

Hope this helps.

DBomrrsm

 

[NoCoolHandle]

<snip>
I've got a huge (couple of million rows) table with a field
</snip>
[blue]
Ok, first - great function DBomrrsm. I think UDF's where one of the greatest additions to sql2k, along with partitioned views on federated servers....

[/blue]John....[blue] Have care using this approach with your table and consider carefully adding one or two more columns so you can index your table for frequent searchs of last name or first name...

The big reason I say be carefull is that you will ALWAYS DO A TABLE SCAN using syntax like..

SELECT Myfield1,Myfield2 etc
FROM MYTABLE
WHERE dbo.IsAlpha(Myfield2) = 0

Why? [red]WHERE dbo.IsAlpha(Myfield2) [/red] will cause this function to be executed on EVERY row to see if it is 0. As your table grows you will definitly see a performance problem with this query.. for two reasons.. More data to sift through and (most importantly) it needs to do a nested loop on every one of those 1 or 10 million rows that it looks at.. Every time you run the query...

Using a nested subquery might work better if their is an index on the name field as it could use the index on the nested subquery to bring back the rows in the master table..
But that still needs to examine every one of the values even if it doesn't bring back the rest of the row with it..

Another approach could be to build a calculated column that uses the above function as a method of populating the column. This way when you run the query it only needs to examine a 2 byte field to see if it is 0 or 1..

The of course you might definitly want to look at Indexed Views with calculated columns (using the function above to create the calculated column) then you will really see some query performance...


Just a couple of thoughts...


Rob
[/blue]

 

[dbomrrsm]

John/Rob

I agree with the points made by Rob and my solution would be to have a column in the table - like Rob suggests - that is populated with a 0 or 1.

DBomrrsm

 

[dbomrrsm]

BTW - and if you havent already worked it out for yourself a small change to the function will check if a field is alphanumeric.

CREATE FUNCTION [dbo].[IsAlphaNumeric]
(
@Text varchar(255)
)
RETURNS BIT
AS


BEGIN
DECLARE @Letters VARCHAR(255)
DECLARE @Index INT
DECLARE @Max INT
DECLARE @Pass BIT
SET @Letters = ' abcdefghijklmnopqrstuvwxyz0123456789 '
SET @Max = LEN(@Text)
SET @Index = 0
SET @Pass = 1
WHILE @Index < @Max AND @Pass = 1
BEGIN
SET @Index = @Index + 1
IF NOT @Letters LIKE '%' + SUBSTRING(@Text, @Index, 1) + '%' SET @Pass = 0
END
RETURN(@Pass)
END

 

[ESquared]

What's wrong with a simple old

SELECT *
   FROM GiganticTable
   WHERE Main_Name Not Like '%[a-z]%'

to find rows which do not have any alpha characters?

And if you want to find rows where any nonalpha character exists, it would be

SELECT *
   FROM GiganticTable
   WHERE Main_Name Like '%[^a-z]%'

-------------------------------------
A sacrifice is harder when no one knows you've made it.