Solved

search words like

Posted on 2014-10-11
6
102 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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:
Scott Pletcher 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

710 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