How can I get my query to search for a ser first or last name without spelling the name as exactly as it's spelled in the database?

My current query searches for user ids but report users would rather look up users by First name or Last name. With our databases if the names aren't spelled exactly as it is in the database it won't return a responce. How would I write a variable that would return names close to what was typed?


select a.USER_ID, a.USER_NAME, a.DEVICE_SW, a.SUPERVISOR
from CSI.SY_USER a
where (a.USER_INACTIVE_DATE is null)
 and a.KRONOS_ID <> ' '
 and a.supervisor in ('00APOP00',
'00BADDR0',
'00BUCHR0',
'00BUSHA0',
'W8MUTHC0')
and (a.user_ID = @variable('USER ID (Enter Single ID or ALL)')
                or
                @variable('USER ID (Enter Single ID or ALL)') = 'ALL')

order by a.SUPERVISOR,a.USER_ID, a.USER_NAME
Jbancr1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
For which database?  This was posted in both Oracle and SQL Server.

In Oracle take a look at SOUNDEX.  It has issues where it thinks words might sound alike when there is no way but it should help with what you want

select ... where ... and soundex(entered_name) = soundex(table_column);

http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions167.htm#SQLRF06109
0
Jbancr1Author Commented:
slightwv,  we have a oracle database but our reporting tool is Business Objects on MS SQL Server.
0
slightwv (䄆 Netminder) Commented:
>>we have a oracle database but our reporting tool is Business Objects on MS SQL Server.

Doesn't help me much.  What database do you need the syntax for?

Anyway, looks like SQL Server has a SOUNDEX as well:
https://msdn.microsoft.com/en-us/library/ms187384.aspx

I would start with SOUNDEX.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

awking00Commented:
>>we have a oracle database but our reporting tool is Business Objects on MS SQL Server<<
Which database is receiving the query? Using soundex can be helpful, but it is by no means perfect. In either Oracle or SQL Server soundex('Alicia') = soundex('Elisha') will be false while soundex('Alicia') = soundex('Alice') will be true. In SQL Server, there is also a function called difference which compares two soundex values and returns an integer from 0 to 4, where 0 represents no match at all and 4 represents a perfect soundex match. The function difference('Alicia','Elisha') would actually return a 3, which you may find acceptable so your search could be where difference(name_entered,column_name) >= 3. Oracle doesn't have such a function although one can be easily created.
0
Jbancr1Author Commented:
Here's were I wrote the query usying SOUNDEX but how would I write it show the usre would be prompt for name to be entered instead of adding name in query?

select a.USER_ID, a.USER_NAME, a.DEVICE_SW, a.SUPERVISOR
 from CSI.SY_USER a
 where (a.USER_INACTIVE_DATE is null)
  and a.KRONOS_ID <> ' '
  and a.supervisor in ('80APOP00',
 '80BADDR0',
 '80BUCHR0',
 '80BUSHA0',
 'W8MUTHC0')
 and soundex('Angel') = soundex(USER_NAMe)

USER_ID      USER_NAME      DEVICE_SW      SUPERVISOR
      ANGEL COLON RF                           R      
      ANGEL.COLON                              W      
      ANGELA.WILLIAMSON                        W      
      ANGELA WILLIAMSON  RF                    R      
      AMY GLADWELL                             W      
      AMY GLADWELL RF                          R      
      ANGEL RIVERA-SANCHEZ RF REQ295480        W      
      ANGELICA RODRIQUEZ REQ315094             W      
      AMY GULLIVER RF REQ418917                R      
      ANGEL NIEVES RF REQ151404                R      
      ANGEL HOUSEMAN RF REQ302651              R
0
slightwv (䄆 Netminder) Commented:
>>but how would I write it show the usre would be prompt for name to be entered instead of adding name in query

To get a prompt depends on what database/tool you want to do the prompting.  You still have not answered this.  There is no single way to do this across Oracle, SQL Server or Business Objects.  Each of these have their own way to do this.

In Oracle using sqlplus/SQL Developer there are a couple of ways.

There is an ACCEPT command and variable substitution (the &variable_name).

I'm pretty SQL Serer and Business Objects also have ways but we need to know specifically what you are using and how you want to be prompted.

>>Oracle doesn't have such a function although one can be easily created.

May or may not be EXACT matches to difference but I'm not a SQL Server person but Oracle does have a few others:
UTL_MATCH.EDIT_DISTANCE and UTL_MATCH.JARO_WINKLER_SIMILARITY

However in my experimenting with them, they are just a fallible as SOUNDEX.  Just in different ways.
0
Jbancr1Author Commented:
I've decides to use    'and a.USER_NAME Like @variable ('Type Name in Caps and between %  % ') ' it got me as close as I could get.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
What part of SOUNDEX didn't you understand?

If names can be misspelled then LIKE is a horrible way to solve this.

>>@variable ('Type Name in Caps and between %  % ') '

This doesn't appear to be Oracle syntax for anything.
0
Jbancr1Author Commented:
It was the simpliest way  I  understood to do it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.