How to group using like statement

Question

First time poster here, so be kind...

I am trying to create a new column that would group entries from another column that contain strings of text - for example, I would want entries containing "penny" in column A to have the value "Penny" in the new column, entries containing "nickel" in column A to have "Nickel", etc. I'm assuming I'd need to use a LIKE statement for this, as there may or may not be characters before/after the text strings I'm categorizing by (i.e. penny, nickel, etc).

It was recommended to me that I use a CASE statement, but I'm not sure that CASE statements can be used with LIKE. Anyone have any ideas? I'm by no means skilled with SQL, so I may be missing something, but will appreciate any assistance!

When you say _column_, do you mean it's in the same table or do you want to insert values into another table? — edoloughlin, Feb 03 '14 at 23:38

score 1 · Answer 1 · edited Feb 04 '14 at 00:47

1

You could do something like

UPDATE mytable
SET new_column = 
    (CASE
        WHEN original_column LIKE '%nickel%' THEN 'nickel'
        WHEN original_column LIKE '%penny%'  THEN 'penny'
        ELSE 'no match' END);

Your mileage on different SQL dialects may vary; this is off the top of my head.

edited Feb 04 '14 at 00:47

Sean Carpenter

7,681
3
37
38

answered Feb 03 '14 at 23:44

edoloughlin

5,821
4
32
61

score 0 · Answer 2 · answered Feb 03 '14 at 23:57

0

If I understand what you wish to do from the example presented, I believe you can just capitalize the first letter without using Like, which is a very expensive operator.

How about:

select metal, concat(upper(substr(metal,1,1)),substr(metal,2)) as CapitalizedMetal from t1

Here's the sqlfiddle

answered Feb 03 '14 at 23:57

ron tornambe

10,452
7
33
60

That's not quite what I was going for - I was looking to create a new column to categorize my original column by whether they contain certain strings of text. Thank you for your input, though! – user3267838 Feb 04 '14 at 14:34

How to group using like statement

2 Answers2