CODEWITHSUNDEEP
sqloracle-database
Reena
Reena

Reputation: 1

Oracle like ' 124 % string not working

how to find the data like my requirement is col1 is clob column value in col like:-

12.5.4 12.4 12.5 12.4.1 12.5 12.6 12.5 12.5 12.4

i need to find data where 12.4 in beginning, middle and end if I run this query to get 12.4 in middle of the data it's not working

SELECT * FROM TABLE WHERE COL1 LIKE ' %12.4 '

my column is CLOB column need to find the data in CLOB column separated by space

Example

12.4 12.5 12.4.1 

12.5 12.4 12.5

12.4.1 12.4.5 

12.5.1 12.4 12.4.1

I am having the data in CLOB column with separated by space 12.4 may in first value or middle of data or end of data

first row  12.4 12.5 12.6.1
second row 13.5 12.4 12.6.2
third row  12.5 14.5.1 12.4
fourth row 12.4.1 12.6 12.7

I need first 3 rows not fourth row because it's having 12.4.1

Upvotes: 0

Views: 153

Answers (5)

Art
Art

Reputation: 5792

Your explanations, requirements and data is still not clear. But maybe this very simple solution will help you, at least as an idea. Look at row 6 in my example. What is your requirement in this case? And the Kaushik Nayak query works.

WITH test_data AS
(
SELECT 1 rn, '12.4 12.5 12.6.1' str FROM dual
UNION ALL
SELECT 2,    '13.5 12.4 12.6.2' FROM dual
UNION ALL
SELECT 3,   '12.5 14.5.1 12.4' FROM dual
UNION ALL
SELECT 4,   '12.4.1 12.6 12.7' FROM dual
UNION ALL
SELECT 5,   '12.4.1 12.4.6 12.4.7' FROM dual
UNION ALL
SELECT 6,   '12.4 12.4.1 12.4' FROM dual
)
SELECT rn, str FROM test_data
 WHERE str Like '%12.4%'
MINUS
SELECT rn, str FROM test_data
 WHERE str Like '%12.4.%'
ORDER BY 1
/

Output:

12.4 12.5 12.6.1
13.5 12.4 12.6.2
12.5 14.5.1 12.4

Upvotes: 0

jose_bacoy
jose_bacoy

Reputation: 12704

This is working when i test in sql fiddle.

   select *
    from table1
    where instr(replace(col1,' ','|'), '12.4|') > 0
    or    instr(replace(col1,' ','|'),'|12.4|') > 0
    or    instr(replace(col1,' ','|'),'|12.4') > 0;

EDIT: previous answer works in sqlfiddle but not to Reena. I added another answer above which is using instr (find this string on this column). Returns the position of the search string in the given column.

Replace space with pipe then do a regular like query.
    select *
    from table1
    where replace(col1,' ','|') like '12.4|%'
    or replace(col1,' ','|') like '%|12.4|%'
    or replace(col1,' ','|') like '%|12.4';

Upvotes: 0

Dave Costa
Dave Costa

Reputation: 48121

i need to find data where 12.4 in beginning, middle and end

It's not clear from your question whether you want to match any item that contains 12.4 (like 12.4.1) or just an item that is exactly 12.4. I've assumed you want the exact match.

The simplest way to search for a value in the middle only is LIKE '% 12.4 %'. But this will not match if the 12.4 is at the beginning or end of the column value, because there won't be a matching space before/after the value.

A trick to get around this is to concatenate spaces at the beginning and end of the column value: WHERE ' ' || COL1 || ' ' LIKE '% 12.4 %'.

Another approach is to explicitly cover all the possibilities: WHERE ( COL1 LIKE '12.4 %' OR COL1 LIKE '% 12.4' OR COL1 LIKE '% 12.4 %').

Upvotes: 2

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

I suspect you want to match 12.4 only at word boundaries (beginning,middle and end as separate literal) so, use the below REGEXP pattern. 

SELECT * FROM TABLE WHERE 
REGEXP_LIKE(COL1 ,'(^|\s)12.4($|\s)');

Demo

Upvotes: 2

Barbaros Özhan
Barbaros Özhan

Reputation: 65353

Use with double % instead :

SELECT * FROM myTABLE WHERE COL1 LIKE '%12.4%';

This time doesn't matter where your literal is.

Moreoever, may be made parametrical for different values, by using a bind variable :

SELECT * FROM myTABLE WHERE COL1 LIKE '%'||'&x'||'%' -- x := 12.4 or a value else.

Upvotes: 2

Related Questions