All Forums Database
openbox 4 posts Joined 12/13
05 May 2016
Bad character in format or data

Hi I am a newbie in TD. I need your help in identifying the bad character on my table. I'm only expecting negative & positive numbers as my result.
 
column_abc CHAR(19) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT NULL ,
 
Thank you in advance.

openbox 4 posts Joined 12/13
05 May 2016

Is there away to find a string or special character in this column_abc?

dnoeth 4628 posts Joined 11/04
05 May 2016

You could use a REGEXP_SIMILAR, but the easiest way is TO_NUMBER(column_abc), which simply returns NULL if it can't be converted to a numeric value.

Dieter

openbox 4 posts Joined 12/13
05 May 2016

Thank you so much Dieter, you solved my problem.
 

yuvaevergreen 93 posts Joined 07/09
06 May 2016

REGEXP_SIMILAR('2A','^[0-9+]$',i') returns 0
REGEXP_SIMILAR('2121121','^[0-9+]$',i') returns 1
 
Works in >= TD 14 

You must sign in to leave a comment.