CODEWITHSUNDEEP
mysqlselectvarchar
BardsWork
BardsWork

Reputation: 566

MySQL select where varchar length

I'm trying to select all fields where the the length of a field = X

The goal is to see if a field has 15 characters (VARCHAR) and set type=Y

When I test the select I get back 0 rows, which is incorrect.

select *
from payment_options
where char_length(cc_type) = 15

I tried length, char_length, etc but nothing works.

Can anyone guide me in the right direction, please?

Thank You.

Edit: For clarification.

I got it =( I was specifying the wrong field.

What I wanted to do is SET cc_type to Amex if char_length(cc_masked) = 15

UPDATE payment_options
SET cc_type = "Amex"
WHERE char_length(cc_masked) = 15;

Upvotes: 17

Views: 29880

Answers (1)

zamnuts
zamnuts

Reputation: 9582

What you have should work. Perhaps you need to elaborate on "nothing works": empty result set? sql error? what? You should also specify your schema and sample data.

Regardless, I went ahead and created a sql fiddle show how something like this might work: http://sqlfiddle.com/#!2/7242e/8

Schema and Sample Data

CREATE TABLE `payment_options` (
    `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `cc_type` VARCHAR(255),
    `cc_name` VARCHAR(255)
);

INSERT INTO `payment_options` (`cc_type`,`cc_name`) VALUES
    ('fifteen chars01','alpha'),
    ('fifteen chars02','bravo'),
    ('not fifteen chars','charlie'),
    ('fifteen chars03','delta'),
    ('really not 15 chars','echo');

Your Existing Query

SELECT *
FROM `payment_options`
WHERE CHAR_LENGTH(`cc_type`) = 15;

Provides the following result set:

|--------------------------------------|
|  ID  |  CC_TYPE          |  CC_NAME  |
|  1   |  fifteen chars01  |  alpha    |
|  2   |  fifteen chars02  |  bravo    |
|  4   |  fifteen chars03  |  delta    |
|--------------------------------------|

If you want to run the X/Y logic within the query itself, you could use IF (IF() docs, related SO answer):

SELECT  *,
    IF(CHAR_LENGTH(`cc_type`)=15,'Y','X') as `cc_type_modified`
FROM `payment_options`;

Yields:

|---------------------------------------------------------------|
|  ID  |  CC_TYPE              |  CC_NAME  |  CC_TYPE_MODIFIED  |
|  1   |  fifteen chars01      |  alpha    |  Y                 |
|  2   |  fifteen chars02      |  bravo    |  Y                 |
|  3   |  not fifteen chars    |  charlie  |  X                 |
|  4   |  fifteen chars03      |  delta    |  Y                 |
|  5   |  really not 15 chars  |  echo     |  X                 |
|---------------------------------------------------------------|

Upvotes: 22

Related Questions