Check if a value starts with one of the value of a column-CodePudding

Hi I'd like to check if a value starts with one of the value of a another column.

t1  | t2  
----------  
3253 | 123  
1234 | 000  
9876 | 932

So here for example I should have True for the value 1234 because it starts with 123. I should have false for the other values.

I can't find any solutions. Thank you in advance for you help !

I already tried :

t1 LIKE (t2 || '%')  
starts_with(t1,t2)  
starts_with(t1, (select t2))

CodePudding user response：

That works for me :

-- To match your example
WITH T (t1,t2) AS
(
SELECT * FROM (VALUES (3253,123),(1234,000),(9876,932))t(t1,t2)
)

-- What your query should like
SELECT TAB1.t1,TAB1.t2, Tab2.t2
FROM T AS Tab1
CROSS JOIN T AS Tab2
WHERE CAST(TAB1.t1 AS STRING) LIKE (CAST(TAB2.t2 AS STRING)   '%')

CodePudding user response：

Using t1 LIKE (t2 || '%') should be able to get you close to what I think you need, however maybe you are just missing a bit of script logic?

Ignoring any platform specific language (I don't use Big Query), this is just to show the logic that might help you get the result you desire

With the data as:

create table my_table
(t1 number,
 t2 number);
insert into my_table values(3253,123);
insert into my_table values(1234,000);
insert into my_table values(9876,932);

You can use a case statement wrapped in a sum to count the matches using like t2||'%. Any value in the resulting column that is 1 or greater should be read as True, and value of 0 as False.

SELECT a.t1,
       Sum(CASE
             WHEN a.t1 LIKE b.t2
                            || '%' THEN 1
             ELSE 0
           END) AS starts_with
FROM   my_table a,
       my_table b
GROUP  BY a.t1

This gives the output

T1  STARTS_WITH
9876    0
1234    1
3253    0