How to extract string between quotes, with a delimiter in Snowflake

See also questions close to this topic

• Why does this Sql query shows the possible changes but does not implement it?

  So, I want to change the prefix of my tables and the following command shows the possible changes that will take place which seems alright but does not seem to implement it.

  SELECT Concat('RENAME TABLE ', TABLE_NAME, ' TO fan_', SUBSTRING_INDEX(TABLE_NAME, 'pc_',-1), ';') FROM information_schema.tables WHERE table_name like 'pc_%' and table_schema='testdbhere'

  Moreover, this isn't a writing privilege issue as changing the tables name individually works perfectly from the same user.
• how to get weeklytotal and yesterday record in mysql in one table

  Hi Everyone i am trying to implement query to get weekly and yesterday data in same table, dummy output i have shared below, if yesterday not exist as per employee_id it should we showing 0 also as per my table week start from monday and end at sunday.please help me out how to query this get weekly_total and yesterday record and one table.

  Table name-dailydata-

  Sample data

  employee_id	date	total
  20	2022-04-25	10
  20	2022-04-26	20
  20	2022-04-27	20
  20	2022-04-28	10
  20	2022-04-29	20
  20	2022-04-30	30
  20	2022-04-31	40
  20	2022-05-01	50
  40	2022-04-26	20

  expected output

  employee_id	weekly_total	yesterday_record
  20	200	40
  40	20	0

  mysql query to get weekly data

  select employee_id,sum(total) as week_total from dailydata where date between '2022-04-25' and '2022-05-01'
  
• Procedure to display the Employees of a specific Department

  Create a stored procedure, to display the lastnames as "Name" from the employee table. The requirement is as below: The name of the Procedure : EmployeesDept

  Name of the argument as Input : DeptNo

  Write the code to create the procedure

  Schema

  I wrote the Query like this but i didnt get expected output

  CREATE  PROCEDURE Employeesdept(@Deptno varchar)
  AS
  Begin
  SELECT lastname as name
  from employee
  where workdept= 'D21'
  end
  

  Expected output:

  Name --------------- Pulaski Jefferson Marino Smith Johnson Perez Monteverde Name ---------------
• How to get the domain and string to the first slash

  For example like this

   url = https://example.com/test/myname/myname.jpg
   result = re.search('(?:https?://)?(?P<host>.*?)(?:[:#?/@]|$)', url)
  

  I can get the example.com with this.

  However I want to get the example.com/test (string before first /)

  how can I do this?
• How to check if data URI is valid in PHP?

  How to validate data uri ex: "data:image/svg+xml,%3Csvg%20xmlns='http://www.w3.org/2000/svg'%20viewBox='0%200%2..."
• Extracting Date from String with Regular Expressions for Python

  Can anyone tell me why the following isn't working:

  I am reading lines from a file, some lines have a date at the beginning and some lines do not. I wanted to use regex to detect if a line I am reading in from the file has a date at the beginning, if it does then I want to do some further parsing. If it does not, I just want to ignore it. The following is a sample line:

  " JAN 01 19 BALANCE FORWARD: 32 CHQ PLAN 33 222.13 "

  When I use the re.findall, it appears my regular expression is incorrect as I am hoping to extract the date "JAN 01 19" but instead all I get returned is "JAN":

  line = " JAN 01 19 BALANCE FORWARD: 32 CHQ PLAN 33                        222.13        "
  print(re.findall(r"(JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC) \d{2} \d{2}", line))
  

  Output:

  ['JAN']
  

  What I am hoping to see is:

  ['JAN 01 19']
  

  From what I understand of regex, the (JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC) part should be checking for one of these months in the string, I then have a space and then I am looking for a 2 digit number with \d{2}, then another space and then another 2 digit number with \d{2}.

  I tried testing this out on https://regex101.com/ and I seem to get a match of 'JAN 01 19' so I am really unsure why the regular expression is not working when I move this to Python.

  
• How to convert a string into a url-friendly string in Snowflake SQL?

  I am working in Snowflake and have a column pets that I'm trying to put into a url friendly format so they can be concatenated to links within Metabase. Example:

  'Tom & Jerry' -> 'Tom%20%26%20Jerry'

  Is there a function in Snowflake that does this?
• Snowflake CONCAT strings with DISTINCT values

  I am doing a merge where a string exists in my target table.

  To this I want to concat a new delta value which is a ; separated string list in my

  WHEN MATCHED .... UPDATE clause
  
  eg.
  
  set TARGET_VALUE = 'abc';
  
  set DELTA = 'abc;def';
  

  After concat:

  OUTPUT should be abc;def;
  
  
  select CONCAT($TARGET_VALUE,';', $DELTA);
  
  
  
    Gives me    abc;abc;def.
  

  Is there a way to achieve this inline without converting the strings to Array and following: How to concatenate arrays in Snowflake with distinct values? ?

  I want to avoid creating a custom function or snippet and check if there is an inline way to achieve this.

  I wrote a UDF to achieve this, but is there an inline way ?

  set TARGET_VALUE = 'xyhx;abc';
  
  set DELTA = 'abc;def';
  
  select string_distinct_concat($TARGET_VALUE, $DELTA);
  
  // Distinct Concatenate
  create or replace function string_distinct_concat(target varchar, delta varchar)
      returns variant
      language javascript
      comment = 'Returns a distinct concatenation of two strings'
  as
  $$
  
       if (TARGET == null) {
          return DELTA
       }
        
       if (DELTA == null) {
          return TARGET
       }
       const target_array = TARGET.split(';');
       const delta_array = DELTA.split(';');
       const set = new Set([...target_array,...delta_array])
       return [...set].join(';');
  $$
  ;
  
• Snowflake parsing JSON and add aggrate column

  have a json field

  [
      {'id':'1', 'name':'test1', 'address':'123 Main St'},
      {'id':'1', 'name':'test2', 'address':'1404 Burke St'}
  ]
  

  This is how the records are arriving and I would want to parse the json and add additional column that is order which will tell if it's first record or second

  so far i have:

  select to_variant(parse_json(column)):id, to_variant(to_json(column)):name, to_variant(to_json(column)):address from the table
  

  but cannot add the order column