RE: OT-SQL matching

[Eric Creese]

hmmmmm not sure unless I give it a try.

Here is my revised SQL, I got rid of the substring on the last name and went 
with matching the first three letters

SELECT 
n.[last name] as lastname,x.perlname, 
n.[first name] as firstname,x.perfname, 
n.[street 1],x.addrline1,n.city,n.state ,x.city,x.state 
FROM NEW_STUDENTS n, EAA_CHECK x
WHERE LEFT(n.[first name],1) = left(x.perfname,1)
AND n.city =x.city
AND n.state = x.state
AND LEFT(n.[street 1],3)= left(x.addrline1, 3)
AND LEFT(n.[last name],3) = left(x.perlname,3)

-----Original Message-----
From: Adrian Lynch [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 23, 2005 4:30 PM
To: CF-Talk
Subject: RE: OT-SQL matching


Here's something I got from the web, so not claiming credit for the
function.

/*
        SELECT * FROM Split('Hella:Cool', ':')
*/

CREATE FUNCTION Split(

        @string NVARCHAR(4000), @delimiter CHAR(1)

) RETURNS @results TABLE( [ID] INT IDENTITY(1,1), Items NVARCHAR(4000) )

AS

BEGIN

        DECLARE
                @index INT,
                @slice NVARCHAR(4000)

        SET @index = 1

        IF @string IS NULL RETURN

        WHILE ( @index != 0 ) BEGIN

                SELECT @index = CHARINDEX(@delimiter,@string)

                IF ( @index != 0 )
                        SELECT @slice = LEFT(@string, @index - 1)
                ELSE
                        SELECT @slice = @string

                INSERT INTO @results (
                        Items
                ) VALUES (
                        @slice
                )

                SELECT @string = RIGHT(@string,LEN(@string) - @index)

                IF LEN(@string) = 0
                        BREAK

        END

        RETURN

END

It returns a table so you can select the first part of the name.

Is that any help?

Ade

-----Original Message-----
From: Eric Creese [mailto:[EMAIL PROTECTED]
Sent: 23 February 2005 22:22
To: CF-Talk
Subject: RE: OT-SQL matching


TABLE1
LNAME           FNAME   CITY            ST
DOE JR  JOHN    PHOENIX AZ
GATES           BILL    REDMOND WA
GORE III        AL      BACKWOODS       TN
BRADSHAW        TERRY HOUSTON   TX


TABLE2
LNAME           FNAME   CITY            ST
DOE             JOHN    PHOENIX AZ
GATES           BILL    REDMOND WA
GORE            AL      BACKWOODS       TN
BRADSAW TERRY HOUSTON   TX

I will wnt to try to match the two together but in table one there is a
suffix in the last name proceded by a space. need to get those out of there.
I would match all accept for the last row

-----Original Message-----
From: Adrian Lynch [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 23, 2005 4:13 PM
To: CF-Talk
Subject: RE: OT-SQL matching

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 266.4.0 - Release Date: 22/02/2005




~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:196225
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54