Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gunsandbutter06
Starting Member
4 Posts |
 Posted - 2006-07-27 : 12:34:51
|
| I am trying to return data based on an input parameter which is a users last name. I was previously using a LIKE, but the search was very slow and returned names which were not matching exactly either (ex: entered Smith, but also got results for Smithson etc). I am having trouble figuring out how to search for a users last name and return the correct data because of the way the user names are stored in our system. The issue is that the field in the system that stores the name (u.usercn) is all one string. For example: 'Smith/, John' All of these names have the / or ,. What I was wondering is how I can search for last name exactly and it will only return the users with the last name that was entered. Is there a way I can just search on the name up to the / or ,? I also really need to speed up the search. I am pretty new to this, so any ideas? My current code is listed below. select Distinct ui.username as UserID, c.computer_name as WorkStation, u.usercn as UserName, u.UserOURDN from UserInfo ui inner join Dim_Computer_Computer c on c.computer_name = ui.wsname inner join ADUser u on u.samaccountname = ui.username where (u.usercn like @UserLastName + '%') and ui.OS in (select OS from tblFilterOS) Thanks! |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-07-27 : 12:47:34
|
Try adding the bit in red...where (u.usercn like @UserLastName + '[/,]%') To improve the speed, you should normalize your data - so have a column for Surname and a column for FirstName, and put your data in that way.  Ryan Randallwww.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that's the hard part. |
 |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-07-27 : 12:47:51
|
| I would recommend creating and populating a separate "last name" column. Exact matching should be a lot easier afterward. |
|
|
|
gunsandbutter06
Starting Member
4 Posts |
Posted - 2006-07-27 : 13:51:11
|
| Thanks for the info! One of the problems with being able to create a seperate column for "last name" is that they will not let me do it. It is pulling straight from a table that uses live and updated data and they do not want to change anything within it. So is there any other way to speed up performance without creating new columns in the table? |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2006-07-27 : 14:10:27
|
| Is there an index on the usercn column? Since your using a like with a wildcard at the end the database can still use an index on this column to possibly speed up the query. |
|
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-07-27 : 16:17:34
|
| Will they let you create an indexed view? |
|
|
|
|
|
|
|
|
Searching string issue