Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

search words like

Posted on 2014-10-11
6
Medium Priority
?
108 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 66

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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

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

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 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 70

Accepted Solution

by:
Scott Pletcher earned 1000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

916 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