CODEWITHSUNDEEP
sqlsql-like
Toshi
Toshi

Reputation: 2608

SQL Case insensitive IN search

I have this Table "Table" with content:

+--------+
| Serial |
+--------+
| d100m  | <- expected result
| D100M  | <- expected result
| d200m  | <- expected result
| d300L  |
| D400R  |
+--------+

There are case inaccurate serial numbers stored.

Currently I am selecting there with a statement like

SELECT Serial FROM Table WHERE Serial LIKE 'D100M' OR Serial LIKE 'D200M';

But isn't there a easier way instead of OR Serial LIKE OR Serial LIKE OR Serial LIKE there are alomst 30 numbers i have to compare with.

Something like this

SELECT Serial FROM Table WHERE Serial LIKE IN ('D100M', 'D200M')

Upvotes: 3

Views: 13590

Answers (6)

Amit
Amit

Reputation: 9

you may check following:

SELECT Serial
FROM Table
WHERE (Serial collate SQL_latin1_general_cp1_cs_as)
      IN ('D100M', 'D200M', 'd200m');

It should get correct and desired information.

Upvotes: 0

Th3Nic3Guy
Th3Nic3Guy

Reputation: 1979

Use the below query format:

select * from dual where upper(DUMMY) in (SELECT upper(x.split_values)
FROM
  (WITH T AS
  (SELECT 'A,B,C,D,E,F' STR FROM DUAL
  )
SELECT REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL) SPLIT_VALUES
FROM T
  CONNECT BY LEVEL <=
  (SELECT LENGTH (REPLACE (STR, ',', NULL)) FROM T
  )
  ) x
)

The query in the IN clause converts your comma separated list to rows...the final selection in x.split values is converted to upper and then returned as columns.

finally it is supplied to IN clause as a mini table...

This is Oracle Specific.

in case of MS SQL this link may be useful to perform same ops...: http://sqljason.com/2010/05/converting-single-comma-separated-row.html

Upvotes: 0

gusviantoko dali purwanto
gusviantoko dali purwanto

Reputation: 11

I assume that you want to get the records with ignoring the case. You can use upper or lower function and do something like:

SELECT Serial FROM Table WHERE Upper(Serial) IN ('D100M', 'D200M')

Or

SELECT Serial FROM Table WHERE Lower(Serial) IN ('d100m', 'd200m')

Upvotes: 1

Tedo G.
Tedo G.

Reputation: 1565

as long as you do not use wildcards or other like operator characters, you can use this script:

SELECT Serial FROM Table WHERE upper(Serial) IN ('D100M', 'D200M')

other wise, you need a full text search.

Upvotes: 1

dns_nx
dns_nx

Reputation: 3943

SELECT Serial FROM Table WHERE Serial IN ('D100M', 'D200M',<addAllSerialsHereCommaSeparated>)

UPDATE: If all serials should be checked in upper case, you could use:

SELECT Serial FROM Table WHERE upper(Serial) IN ('D100M', 'D200M',<addAllSerialsHereCommaSeparated>)

Upvotes: 4

user330315
user330315

Reputation:

The easiest way would be:

SELECT Serial 
FROM Table 
WHERE upper(Serial) in ('D100M', 'D200M');

That won't however use an index on the serial column.

So if performance is a concern you would need to create an index on upper(serial).

Upvotes: 9

Related Questions