1

suppose you are selecting a row of a table that contains the field X equal to a certain String, and this string contains spaces

in my case I've 

select * from table_name where serial = '400 TZV 50'

I know this record exists, but the query return me an empty set.

Anyone could help me?

Improve this question
15
  • can you provide the ddl of the table, some sample data where it fails. This should work...and what version of the data base are you using? and what tool do you use to execute your sql? Commented Oct 4, 2018 at 8:15
  • 2
    Could it be that there are some invisible characters in the string? Perhaps select the data via select serial from table_name where serial like '400%TZV%50' and then check the ascii representation of the string... Commented Oct 4, 2018 at 8:22
  • 1
    Other reason might be: Are you sure the data is allready committed or is it only posted in another session, so the data is not yet visible in your sql-developer session... Commented Oct 4, 2018 at 8:24
  • 1
    with like and % instead of spaces I found it, but I need to use it massively, so I can use only the exact strings Commented Oct 4, 2018 at 8:29
  • 1
    @Radagast81 SELECT DUMP( serial ) AS serial, DUMP( '400 TZV 50' ) AS match FROM table_name WHERE REGEXP_LIKE( serial, '400\s+TZV\s+50\s*' ) seems like a much simpler solution to looking at the raw byte values of the string and see why it is not matching. If it is matching too many rows then add ` AND ROWNUM = 1` to aid with debugging. Commented Oct 4, 2018 at 8:50

2 Answers 2

Reset to default
4 
select * from table_name where serial = '400 TZV 50'

I know this record exists, but the query return me an empty set.

For example, if you have the records:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( serial VARCHAR2(64) );
INSERT INTO table_name
  SELECT '400 TZV  50' FROM DUAL UNION ALL                      -- Extra space in middle
  SELECT '400 TZV 50 ' FROM DUAL UNION ALL                      -- Extra space at the end
  SELECT '400 TZV 50' || CHR(13) || CHR(10) FROM DUAL UNION ALL -- CR/LF at the end
  SELECT '400' || CHR(9) || 'TZV 50' FROM DUAL;                 -- Tab instead of space

To find the records that you think should match. You can either use LIKE:

SELECT serial, '400 TZV 50' AS match
FROM   table_name
WHERE  serial LIKE '400%TZV%50%'

or REGEXP_LIKE:

SELECT serial, '400 TZV 50' AS match
FROM   table_name
WHERE  REGEXP_LIKE( serial, '400\s+TZV\s+50\s*' )

Which both output:

Results:

|       SERIAL |      MATCH |
|--------------|------------|
|  400 TZV  50 | 400 TZV 50 |
|  400 TZV 50  | 400 TZV 50 |
| 400 TZV 50   | 400 TZV 50 |
|              |            |
|   400 TZV 50 | 400 TZV 50 |

then you can look and see whether the values are different.

If you cannot see why it is not matching then use the DUMP function to get the underlying byte values in the data types:

SELECT DUMP( serial ) AS serial, DUMP( '400 TZV 50' ) AS match
FROM   table_name
WHERE  REGEXP_LIKE( serial, '400\s+TZV\s+50\s*' )

Which outputs:

Results:

|                                            SERIAL |                                        MATCH |
|---------------------------------------------------|----------------------------------------------|
|    Typ=1 Len=11: 52,48,48,32,84,90,86,32,32,53,48 | Typ=96 Len=10: 52,48,48,32,84,90,86,32,53,48 |
|    Typ=1 Len=11: 52,48,48,32,84,90,86,32,53,48,32 | Typ=96 Len=10: 52,48,48,32,84,90,86,32,53,48 |
| Typ=1 Len=12: 52,48,48,32,84,90,86,32,53,48,13,10 | Typ=96 Len=10: 52,48,48,32,84,90,86,32,53,48 |
|        Typ=1 Len=10: 52,48,48,9,84,90,86,32,53,48 | Typ=96 Len=10: 52,48,48,32,84,90,86,32,53,48 |

And you can see that the values are different (ignore the typ value as one is a string literal and the other is a VARCHAR2 stored in a table):

  • the first row has a different length from an extra space in the middle:

    Typ= 1 Len=11: 52,48,48,32,84,90,86,32,32,53,48
Typ=96 Len=10: 52,48,48,32,84,90,86,32,53,48
           ^^                          ^^

  • the second has a different length from an extra space at the end:

    Typ= 1 Len=11: 52,48,48,32,84,90,86,32,53,48,32
Typ=96 Len=10: 52,48,48,32,84,90,86,32,53,48
           ^^                               ^^^

  • the third has a different length from CR/LF characters at the end:

    Typ= 1 Len=12: 52,48,48,32,84,90,86,32,53,48,13,10
Typ=96 Len=10: 52,48,48,32,84,90,86,32,53,48
           ^^                               ^^^^^^

  • and the last row has an ASCII value of 9 (tab) in the 4th character when an ASCII value of 32 (space) is expected.

    Typ= 1 Len=10: 52,48,48,9,84,90,86,32,53,48
Typ=96 Len=10: 52,48,48,32,84,90,86,32,53,48
                        ^^

Once you have worked out why your query is not matching then you can adapt it to either match the actual data or, if the data is incorrectly formatted, you can fix the values in the table to be of the expected format.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

ok, thanks for the explaination, but how I can find my data if I have this string in a sequence? I have hundred of records to search
@RobertoCaravelli Work out what your data should look like - is it always 3 digits then space then 3 characters then space then 2 digits - if it is and your actual data does not match that format then you need to fix the data stored in your tables (and look at how it got malformed to start with). If it is valid to have extra delimiting spaces (or other white space characters) or to have trailing characters then you cannot use your query and will have to match on the regular expression or somehow process your input to be in a standardised format when you search it.
1

this will work:

select * from table_name where serial like '400%TZV%50';
Improve this answer

2 Comments

That is not the same query as the provided, that should actually work. Question is why it doesn't work - not to get some similar result query...
The query you are giving is not doing the same as the original query - it might get results but depending on the data not only the desired results but more. So the question is: why does the correct query above not give the desired results and therefore it has to be debugged, what the reason is, why the query doesn't work as expected...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.