x

How do I search for a specific letter in a table?

Iám so new in this, and learning... I could use som help to a problem. I want to search in a table. I´m searching in for lastnames with the letter s in it. How do I do that? (Oracle SQL)
I wrote:

select fnamn,enamn  
from customers
where enamn LIKE 's%';
I have tried a sevral combinatins now...
more ▼

asked Sep 24, 2012 at 09:22 AM in Default

mallan gravatar image

mallan
50 2 3 4

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
You're close. To search for the letter 's' anywhere in the name, you need to add an additional wild card to your search like this: '%s%'. That will find it anywhere within the name. But, understand, that will also lead to a scan of the table and possibly slow performance.
more ▼

answered Sep 24, 2012 at 09:43 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98k 19 21 74

(comments are locked)
10|1200 characters needed characters left

Grant is right - it won't be performant, but it is the answer...

SELECT fnamn, enamn FROM Customers WHERE enamn LIKE '%s%'

As you can see, it's all but identical to the code you showed.

The % wildcard matches zero or more characters; there's also an _ wildcard that matches a single character.

You can also build up not-quite-regular-expressions:

SELECT enamn FROM Customers WHERE enamn LIKE '%[xyz]%'

This will show you all names where there's a letter from what I've heard described as "the arse end of the alphabet".

There are some other tricks to learn - see the documentation for LIKE for more ideas.

more ▼

answered Sep 24, 2012 at 10:10 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

Excellent answer, but doesn't performant mean an actor in a play? Ha!
Sep 24, 2012 at 10:17 AM Grant Fritchey ♦♦
I have no idea - I've spent too much time talking to pointy-haired bosses over the years, and it has warped my vocabulary...
Sep 24, 2012 at 11:35 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x984
x343
x4

asked: Sep 24, 2012 at 09:22 AM

Seen: 748 times

Last Updated: Sep 24, 2012 at 01:05 PM