0

How to find non-ASCII symbols in a string ? (We are using DB2)

We have tried following select statement but it is not working.

SELECT columnname
FROM tablename
WHERE columnname LIKE '%[' + CHAR(127) + '-' + CHAR(255) + ']%' 
COLLATE Latin1_General_100_BIN2
Pratham
  • 1,522
  • 1
  • 18
  • 33
ramya krishna
  • 1
  • What do you mean by non-ASCII ? chars from 127 to 255 range are also part of ASCII. What db2 version and please tag the platform as well (db2-luw, z, i ...) – Samuel Pizarro Jun 04 '20 at 03:20
  • [Duplicate question](https://stackoverflow.com/questions/59473002/how-to-find-ascii-of-every-character-in-string-using-db2-function/59479976#59479976) – Mark Barinstein Jun 04 '20 at 06:14

1 Answers1

0

I guess you were trying to use CHR() function, instead of CHAR(), which is a data-type.

If you are using a newer db2 version, that has REGEXP functions, you can try using REGEXP_LIKE() function.

Follow an example from samble db: 

 SELECT EMPNO, LASTNAME FROM EMPLOYEE  WHERE REGEXP_LIKE(LASTNAME,'[E-H]')

EMPNO  LASTNAME
------ ---------------
000010 HAAS
000020 THOMPSON
000050 GEYER
000060 STERN
000090 HENDERSON
000100 SPENSER
000110 LUCCHESSI
000120 O'CONNELL
000140 NICHOLLS
000170 YOSHIMURA
000180 SCOUTTEN
000190 WALKER
000210 JONES
000230 JEFFERSON
000250 SMITH
000260 JOHNSON
000270 PEREZ
000280 SCHNEIDER
000290 PARKER
000300 SMITH
000310 SETRIGHT
000320 MEHTA
000330 LEE
000340 GOUNOT
200010 HEMMINGER
200220 JOHN
200240 MONTEVERDE
200280 SCHWARTZ
200310 SPRINGER
200330 WONG

  30 record(s) selected.

All names selected contains letters from E to H, as specified by the search-pattern.

As I didn't have any row containing such ranges.. I updated one of the rows, adding chars 169 and 174 to it. 

Update employee set LASTNAME = ('LEE' || chr(169) || chr(174)) WHERE LASTNAME = 'LEE'

and, using this REGEXP_LIKE function: 

SELECT EMPNO, LASTNAME FROM EMPLOYEE WHERE REGEXP_LIKE(LASTNAME , '[' || CHR(127) || '-' || CHR(255) || ']')"

    EMPNO  LASTNAME
    ------ ---------------
    000330 LEE©®

      1 record(s) selected.

Regards

Samuel Pizarro
  • 267
  • 1
  • 8