SQL Query - Get the number from 2 columns, into a 3rd one

Question

I want to build the 3rd column within a query I have, it's a select query.

I tried some stuff like this:

Also thought of regex... It's for big data, so I'm concered about being efficient. Also though of a case switch... I'm using big query btw. Thoughts?? Thanks!

What database are you using? – Gordon Linoff Aug 05 '20 at 00:54 — Gordon Linoff, Aug 05 '20 at 00:54

score 0 · Answer 1 · answered Aug 05 '20 at 00:55

0

If I understand correctly, the logic you want is:

select (case when regexp_like(col1, '^[0-9]+$' then col1 else col2 end)

answered Aug 05 '20 at 00:55

Gordon Linoff

1,242,037
58
646
786

are regex efficient?, and this it work for big query? – Aug 05 '20 at 01:44
@MateusLeão . . . You can try it. – Gordon Linoff Aug 05 '20 at 01:45
if you have big data this approch is not the best because evan you have an where clause on your sql you need to proceed the hole table to generate the wanted column. So yes it works and for not to big table it should be OK. But for large table it is better to change the database structure and add the wanted value to a thrid column – Thomas Aug 05 '20 at 20:33

score 0 · Answer 2 · answered Aug 05 '20 at 20:14

0

well, I actually used case, because I had an additional field that would help me give an order to when to get the number. Case status when 'a' then col1, else col2

something like that. Ty!

answered Aug 05 '20 at 20:14

SQL Query - Get the number from 2 columns, into a 3rd one

2 Answers2