I have a column Lead_Name in which few records have values with multiple line breaks and spaces. One example is:
Now when I search for this lead_name in a query, I don't get any results. Here's the query that I'm using:
SyntaxEditor Code Snippet
SELECT * FROM TEST_TABLE Where Lead_Name LIKE ( 'Test Value ' )
Records without line breaks are returned perfectly fine.
I wouldn't expect them to be found.
Your query is searching for value 'test value', but the data contains 'testxxxxvalue' (where 'xxxx' are the line break characters) - i.e. different data values.
You could do something like:
WHERE Lead_Name LIKE '%test%value%'
Thank you for your reply, Dave.
I don't think LIKE ignores the spaces and line breaks. Unlike =, it looks for the exact same characters.
To confirm, I ran the same query in BTEQ and searched for the lead with line breaks, and it returned the result as expected.
To further confirm, I created Lead_Name: 'Test Value' and searched using the same query and didn't get any results. So apparently LIKE isn't the issue here. I'm thinking it's some setting in Teradata SQL Assistant that's causing this. I would use '%test%value% but the first and last name are part of a single variable in a JAVA service.
Oh and one more thing, the query is working perfectly fine when I run it through the JAVA service.
Firstly my apologies, I mis-read your original post.
In the SQL in your original post, I think your the LIKE clause is effectively an '=' test because there are no pattern characters (% or _).
If running that query in BTEQ finds the data but SQLA does not then you may well be correct about SQLA 'doing something' Although it is quite possibly the api layer 'doing something' When run using SQLA are you using ODBC or NET?
One way to track down what is happening could be to use DBQL to look at the SQL that is received by the dbms.