如何从列中提取数据并创建新列?

Question

我需要使用promodescriptioncolumn创建一个名为" Feature "的新列

特征-这必须从promodescription属性中提取，值将从

下面

1 week foyer,2 week shelf
1 week foyer, 3 weeks shelf
1 week Golden T,2 weeks shelf
2 weeks Golden T,1 week shelf
Bulk stack
Bulk stack 1
Bulk stack 1
Event space
FOS
Foyer
GE7 151 stores
Gondala End
Ladder rack
Plinth
Shelf
Foyer & shelf
Shelf & foyer
Stack

NA

例如

tbody> <<tr>

Promodescription	Feature
P18 4 r工作频率2.25美元货架	货架
P18 4R QSS $3.25门厅和架子	门厅和架子

这个可以产生您想要的确切输出:

select INITCAP(TRIM(REGEXP_SUBSTR( Promodescription, '[^$.0-9]*$'  ))) Feature from 
values (  'P18 4R QSS $2.25 Shelf'),
('P18 4R QSS $3.25 foyer and shelf') tmp(Promodescription );

tbody> <<tr>

FEATURE
货架
门厅与书架

如果字符数保持不变(P18 4R QSS $2.25)，您可以尝试SUBSTR.

创建或替换varchar表insert into addcompcolumn values ('P18 4R QSS $2.25 Shelf')，('P18 4R QSS $3.25 foyer and Shelf');

alter table addcompcolumn ADD column feature varchar as (select substr(a, 17, 16) from addcompcolumn);

select * from addcompcolumn;

特性架子

门厅和架子

https://docs.snowflake.com/en/sql-reference/sql/alter-table-column.html alter-table-alter-column

您也可以通过编程方式或与其他字符串函数一起尝试

如果我们创建"已知标记"表

create or replace table key_words as 
select * from values
('1 week foyer,2 week shelf'),
('1 week foyer, 3 weeks shelf'),
('1 week Golden T,2 weeks shelf'),
('2 weeks Golden T,1 week shelf'),
('Bulk stack'),
('Bulk stack 1'),
('Bulk stack 1'),
('Event space'),
('FOS'),
('Foyer'),
('GE7 151 stores'),
('Gondala End'),
('Ladder rack'),
('Plinth'),
('Shelf'),
('Foyer & shelf'),
('Shelf & foyer'),
('Stack')
t(feature);

，然后创建"测试数据"表上做一些例子:

create table main_data as 
select * from values
(100050635, 'P1B 4R QSS $2.25 SHELF'),
(100050638, 'P1B 2020 9R Natural $4.25 Shelf'),
(100050161, 'P16 9R CC $3.75 Foyer & Shelf')
t(promo_id, promodescription);

一个简单的开始解决方案是注意"Shelf"是在两种不同的情况下，并使用大小写不敏感的ililike

select m.promo_id,
m.promodescription,
f.feature
from main_data as m
left join key_words as f
on m.promodescription ilike '%'||f.feature;

对于我给出的示例数据给出:

tbody> <<tr>

PROMO_ID	PROMODESCRIPTION	FEATURE
100050635	P1B 4 r工作频率2.25美元货架	货架
100050638	P1B 2020 9R Natural $4.25 Shelf	Shelf
		P16 9R CC $3.75门厅&架子上	货架
		P16 9R CC $3.75门厅&架子上	大厅,架子上