0

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 (nevermind 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:

enter image description here

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 MS 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)) + '%'

How can I do taht in Teradata SQL? Could you modify my code which work on MS SQL Server or suggest your own solution which work on Teradata SQL ?

Thom A
  • 88,727
  • 11
  • 45
  • 75
dingaro
  • 2,156
  • 9
  • 29
  • At the least, use Teradata functions instead of SQL Server such as `SUBSTRING(a,b,c)` -> `SUBSTR(a,b,c)` and `CHARINDEX(x,y)` -> `INDEX(y,x)` and `LEN(x)` -> `LENGTH(x)`. Teradata also offers SUBSTRING and POSITION functions that use ANSI syntax with similar behavior: `SUBSTRING(a FROM b FOR c)` and `POSITION(x IN y)` – Fred Jul 02 '21 at 15:10
  • So could you modify my code which I mentioned in question that works on MS SQL and do not work on Teradata, please ? – dingaro Jul 02 '21 at 15:18
  • Fred, I have error: SUBSTR: string subscript out of bound whant can I do ? – dingaro Jul 02 '21 at 15:33
  • Most likely you have a string with no space character so `INDEX` returns zero. Perhaps `NULLIFZERO(INDEX(x,y))` to skip those rows? Also note that Teradata concatenation operator is `||` or `CONCAT(...)` not `+`. – Fred Jul 02 '21 at 16:09
  • `where position(strtok(col2, ' ', 1) in col1) > 0 and position(strtok(col2, ' ', 2) in col1) > 0` should work. No UPPER needed (unless you run you session in ANSI mode) – dnoeth Jul 02 '21 at 21:35
  • dnoeth I have error using your code: STRTOK: InputString or Delim length is 0; OR toknum parameter is no larger than 0, what an I do ? should I add nullifzero somewhere ? – dingaro Jul 02 '21 at 21:46
  • dnoeth, what do you think about above error ? – dingaro Jul 02 '21 at 21:49
  • Ops, I didn't know STRTOK fails for empty strings, apply `strtok(nullif(col2,''), ' ', 1)` – dnoeth Jul 03 '21 at 09:22

0 Answers0