Link to home
Create AccountLog in
Avatar of toooki
toooki

asked on

Check for certain characters in Oracle string

I want to check for the characters .,_-01-9A-B in my table Tab's field F2.

I wrote the query but it is returning non-null value for the "good" field content. For example:
For a field value of "4.4.0-186" (without quote) is is returning 2. For "MLS2GC_U6_0.24.0" it is returning 4.

Do not understand why.

But if I do select (LENGTH(TRIM(TRANSLATE('MLS2GC_U6_0.24.0', '. ,_-0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',' '))))
from dual; --> It returns null which is right.

Why is it showing incorrect output when querying the table?
Is there any other way to verify if the table's that fields contains any bad values?


select distinct Tab.F1, Tab.F2, (LENGTH(TRIM(TRANSLATE(Tab.F2, '. ,_-0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',' ')))) as Length from MyTable Tab where 
(LENGTH(TRIM(TRANSLATE(Tab.F2, '. ,_-0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',' '))) >= 1)

Open in new window

Avatar of ajexpert
ajexpert
Flag of United States of America image

Avatar of toooki
toooki

ASKER

Is it write syntax to check for values like numberic and letters and . , - _ values:

select Tab.F1 from MyTable Tab
where REGEXP_LIKE (Tab.F1, '^[a-zA-Z-_,.0-9]*$');

It is not returing any values.
Can you give me sample data exists in your table?

Also please specify one more time what output you are expecting (no sql queries)
Avatar of toooki

ASKER

I have attached the sample data for the "F1" field of the table.
There are 25 sample records.

I want output of those records which has any character other than numbers, letters(any case) and these four characters . , _ - and space.

 So the following will be in output:
https://test.test2.com/test/view/RR-dev1/job/integrated_device-yahoo--eng_RR-dev1_linux_nightly/17/
( as : and / are prohibited)
The below will not be in output:
EPU93A_U_93.02.22I EPU93_U_00.34.00
(as . _ letter numbers are all good)
 F1Column.txt

Here i have excluded all of punction symbols

               --------------------------------
                % . , " ' ? ! : # $ & ( ) * ;
                + - / < > = @ [ ] \ ^ _ { } | ~
                --------------------------------


does this resolve your issue?

select F1 from MyTable 
minus
select F1 from MyTable 
where regexp_like (col1, '[[:punct:]]' );

Open in new window

Avatar of toooki

ASKER

Sorry, no it does not.
First it does not exclude the punctuation symbols (: / are in output values)
Secondly , . _ - and space symbols are good and a string containing these or a-zA-Z0-9 won't be in output.
 
Avatar of toooki

ASKER

Sorry it does exclude the punctuation symbols.
But excludes , . _ - as well.
So you want to include , . _ -
Avatar of toooki

ASKER

I mean say sample input records are:
Test1Line1,.Test
Test2Line2Test#test99
Test2Line2_Test-test.09
Test123_%Driver23
Testhttp://yahoo.com
Sample_02.20-35.9,Sample123

The output will be the bad ones:
Test2Line2Test#test99
Test123_%Driver23
Testhttp://yahoo.com
try this


select * from MyTable 
where regexp_like (col1, ('#|%|.com'))

Open in new window

Avatar of toooki

ASKER

Above was a sample.
What about input of
Test~
Test@
Test#
Test$
Teat^

and so many others that needs to be in the output....
go on adding !@ but ^ and $ needs to be handled seperately

Let me know it this works, I will provide for ^ and $ later
select * from MyTable 
where regexp_like (col1, ('#|%|.com|!|@'))

Open in new window

Avatar of toooki

ASKER

Well if I have to do that way, I could try this:
How do I add ^ and $ check in it? Reqular way wont work?

select * from MyTable
where col1 like '%~%'
or col1 like '%!%'
or col1 like '%@%'
or col1 like '%#%'
or col1 like '%$%'
or col1 like '%&%'
or col1 like '%^%'
or col1 like '%*%'
or col1 like '%(%'
or col1 like '%)%'
or col1 like '%+%'
or col1 like '%`%'
or col1 like '%=%'
or col1 like '%[%'
or col1 like '%]%'
or col1 like '%{%'
or col1 like '%}%'
or col1 like '%|%'
 or col1 like '%\%'
 or col1 like '%/%'
or col1 like '%?%'
or col1 like '%>%'
or col1 like '%<%'
or col1 like '%:%'
or col1 like '%;%'

SOLUTION
Avatar of ajexpert
ajexpert
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account