Solved

search words like

Posted on 2014-10-11
6
96 Views
Last Modified: 2014-10-20
Hello,
I would like to search for
1) words that start with a specific letter/s. For example, if I have record "William" and another "Howard" then I would like only to get William when I search for "w".
2) I have names of people and I would not like to search for the last name.
For example: If I have a record: John Howard and search for 'H' I would not get result, but I would get result if the name were Howard John. The same goes if the person has three names like Ann Pauline James. I would get result if I look for 'P' but not if I look for 'J'.
Can you please help me with this?
Greetings,
0
Comment
Question by:johnson1
6 Comments
 
LVL 34

Expert Comment

by:Michael-Best
ID: 40374872
Depending on the search engine that you use, can usually define your search by entering the key words  like this :  " ???? " in advanced search options.
Post your search engine for further feedback.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40374891
Please tell us the table schema of what you are searching, and specifically if the first name-last name are in separate columns, or the same columns, or the same column with a bunch of other text.
0
 

Author Comment

by:johnson1
ID: 40375074
I am using sql 2008. The first-last name is in one column (can also be first-middlename-lastname).
In this column there is only the name.

The columns in the table would be like this:   PersonId (int), Fullname(varchar(100)).  Tablename: Person.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:johnson1
ID: 40375075
Sorry I meant MS SQL 2012.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 40375081
>The first-last name is in one column (can also be first-middlename-lastname).

1)  WHERE LEFT(the_name_column, 1) = 'W' will return any value that begins with a W, and w too assuming the column's collation is set to case insensitive.

2) I don't believe you're going to pull that off, as that ass-u-me's that any middle name is what we consider a middle name.  Jean Claude Van Damme?  The Claude is the middle name, Van is part of the last name, and not the middle name, so good luck with that. Rip Van Winkie?  Geordi La Forge?  Charo?  If you can state with certainty the logic here, spell it out for us, otherwise good luck.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 40377351
If you're matching only on the leading letter(s) in the first name, you can do this:

--and search for 'H'
WHERE
    Fullname LIKE 'H%'
--better than using a function, such as "LEFT", on the column


The middle name(s) issue is a bit trickier, because we have to make some assumptions.  How about these rules?  An entry with:
a) 1 word only, will be considered a first name;
b) 2 words only, will be considered first and last name;
c) 3+ words: the first will be considered first name, the second the middle name, and the third+ the last name;
would those rule work for you?
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now