I have in Teradata SQL table like below:
col1 | col2
----------------------------
Adam Nowak PHU | Nowak Adam
AAR Kowal Jan | Kowal Jan
Tomasz Gut | Juk Anna
And I would like to select only these rows where:
- In col1 I have value from col2 + something (never mind before or after value)
- Be aware that order of name and surname is not important so Jan Kowal is the same that Kowal Jan
So as a result I need something like below, so only first and second rows because there are in col1 value from col2 + something else and order of name and surname is not important:
- I used query like below, but my query does not take into account that order of name and surname is not important and for my code Jan Kowal and Kowal Jan are different persons.
where upper(col1) like '%' || upper(col2) || '%'
- Moreover I used code which was perfect to my case and work correctly but only on SQL Server, on Teradata SQL id does not work:
WHERE
upper(col1) LIKE '%' + substring(upper(col2), 1, CHARINDEX(' ',col2)-1) + '%'
AND
upper(col1) LIKE '%' + substring(upper(col2), CHARINDEX(' ',col2)+1, LEN(col2)) + '%'
- I changed it on Teradata code like below:
WHERE
upper(col1) LIKE '%' || substr(upper(col2), 1, nullifzero(index(' ',col2)-1)) || '%'
AND
upper(col1) LIKE '%' || substr(upper(col2), nullifzero(index(' ',col2)+1, length(col2))) || '%'
But above code generate error:
SUBSTR: STRING SUBSCRIPT OUT OF BOUNDS
- I also tried something like below:
where position(strtok(col2, ' ', 1) in col1) > 0 and position(strtok(col2, ' ', 2) in col1) > 0
But it generates error:
STRTOK: InputString or Delim length is 0; OR toknum parameter is no larger than 0,
What can I do? Help me please.
How can I do that in Teradata SQL? Could you modify my code which work on SQL Server or suggest your own solution which work on Teradata SQL?
