0

I have table in Teradata SQL like below:

NAME
Johnson Simon
Whats Ann
Pitt-House Mark
Grahap Michael Josh

This table in column: "NAME" contains name and surname of clients, but be aware that sometimes clients can have two-membered surname or have two names.

As a result I need:

NAME SURNAME NAME_2
Johnson Simon Johnson Simon
Whats Ann Whats Ann
Pitt-House Mark Pitt-House Mark
Grahap Michael Josh Grahap Michael Josh

I think, that it could be done, that we can take first value from column "NAME" to the first white space and it will be value of SURNAME column, and rest values give to NAME_2, but I do not know how to write it in Teradata SQL, or maybe you have other idea how to solve this issue ?

Majid Hajibaba
  • 3,105
  • 6
  • 23
  • 55
dingaro
  • 2,156
  • 9
  • 29

2 Answers2

1

It looks like everything up to the first space is the surname. And everything else is name_2. One method is regexp_substr():

select regexp_substr(name, '^[^ ]+') as surname,
       regexp_replace(name, '^[^ ]+ ', '') as name_2
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • You are right Godon, but I have errro: "Function regexp_replace called with an invalid number or type of parameters, what can I do ? – dingaro Jul 14 '21 at 10:25
  • @koler . . . You can be explicit about the third argument. But I thought Teradata supported the two-argument form. – Gordon Linoff Jul 14 '21 at 12:20
  • @GordonLinoff - you are correct. If the third argument is left out, matches are removed. – Andrew Jul 14 '21 at 15:24
  • The Teradata-supplied REGEXP_REPLACE in TD_SYSFNLIB *does* support the 2-argument form. But there may be some legacy UDF named REGEXP_REPLACE in SYSLIB (which comes before TD_SYSFNLIB in default search order). – Fred Jul 14 '21 at 15:42
1

Use STRTOK

It splits tokens based on the specified list of delimiter characters and returns the nth token, where n is specified by the tokennum argument.

SELECT NAME, STRTOK(your_table.NAME, ' ', 1) as SURNAME, STRTOK(your_table.NAME, ' ', 2) as NAME_2 FROM your_table;
Majid Hajibaba
  • 3,105
  • 6
  • 23
  • 55