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

avatar image

mallan
50 4 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

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(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

avatar image

ThomasRushton ♦♦
39.8k 20 49 52

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.

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:

x1066
x408
x4

asked: Sep 24, 2012 at 09:22 AM

Seen: 1014 times

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

Copyright 2016 Redgate Software. Privacy Policy