netezza - 拆分名字和姓氏 Netezza

Question

我有一个列，其中有一个用空格分隔的客户的名字和姓氏。我会写什么代码来解析他们两个？我试过这个，但它不起作用。

SELECT LEFT (CUSTOMER_NAME, CHARINDEX(' ', CUSTOMER_NAME)) as CUST_1ST_1_NM,
substring(CUSTOMER_NAME,  CHARINDEX(' ', CUSTOMER_NAME)+1, len(CUSTOMER_NAME)-(CHARINDEX(' ', CUSTOMER_NAME)-1)) as CUST_LAST_1_NM
from CUSTOMER_TABLE

谢谢!

Answer 1

像这样使用 INSTR 和 SUBSTR 是第一个想到的方法。

select * from test_table;
 CUSTOMER_NAME
---------------
 foo bar
(1 row)

select substr(CUSTOMER_NAME, 0, instr(CUSTOMER_NAME, ' ')) first_name,
substr(CUSTOMER_NAME,instr(CUSTOMER_NAME, ' ') +1)   last_name
from test_table;
 FIRST_NAME | LAST_NAME
------------+-----------
 foo        | bar
(1 row)