Search column with multiple line breaks in data.

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Teradata Employee

Search column with multiple line breaks in data.

Hi. 

 

I have a column Lead_Name in which few records have values with multiple line breaks and spaces. One example is: 

'Test

  Value

'

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. 

 

3 REPLIES
Senior Apprentice

Re: Search column with multiple line breaks in data.

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%'

HTH
Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Search column with multiple line breaks in data.

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. 

 

Senior Apprentice

Re: Search column with multiple line breaks in data.

Hi,

 

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.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com