SQL Query of name

[dogboydelta]

The website I am working on need to search name by first two letters of last name. The name is in a single field in the database called 'name', so the
String would be 'Bob Smith', and the user would type in 'SM' and look for a match. How would I do this? I'd prefer to do it in an SQL Query entirely, but if I have to do some code manipulation that is possible too.

Thanks.

[{yak}]

You can use sql's LEFT() function:
select flds from table where left(fldname, 2) = yourValue

Good luck

[dogboydelta]

Thanks, that's a start, but the tricky part is the string is the full name, yet the
two letters need to search on the last name, ex. "Bob Smith" needs to search on SM,
which is in the middle of the string.

[Lord_Rat]

WHERE FIELDNAME LIKE '% ' + @USERCRITERIA + '%'

Where @USERCRITERIA is a variable filled with, in this case: 'SM'

Note that LIKE queries are slower than most other queries and you should avoid them when you can.

As a result, if this application is high-peformance, I recommend you create a new field in your table called LASTNAME that is a VARCHAR(SomeSize) and then run the following in Query Analyzer:

CREATE TRIGGER TABLENAMEUPDATETRIGGER
ON TABLENAME
FOR INSERT, UPDATE
AS
BEGIN
IF UPDATE(FIELDNAME)
UPDATE TABLENAME SET LASTNAME = SUBSTRING(FIELDNAME, CHARINDEX(' ', FIELDNAME) + 1, 8000)
END

The result of which is that you can use the above LEFT query on the LASTNAME column, which is much faster.

If this is super-high perfomance, you should be using the Soundex function with a linked one-to-many relationship table. But if you are writing such an application, then I doubt I would have to be that sepecific as the requirements document should already have been. =)