I have a table of email addresses: How can I find and replace the email format so example.person@gmail.com -> example.person_gmailcom@test.com? E.g: Results in example.person@test.comgmail.com Playground here: https://dbfiddle.uk/GnIfomiO Answer This is probably most simply done by splitting the email addr…
Tag: sql
Oracle chr() diferent returns from SQL engine versus PL/SQL engine, why?
Same function, same argument, different outputs: PL/SQL procedure successfully completed. Answer In your case, chr() appears to be performing an implicit conversion to binary_float (IEEE 754 floating-point representation) as the datatype, as opposed to number (ANSI SQL NUMERIC). This results in different roun…
Calculate number of watches by number of users in sql
I want to write 1 query that calculates the frequency buckets for each Ad. The frequency buckets would be the number of users who watched the Ad 1 time, 2 times, 3 times, etc. The table is: AdId UserID Watch Date 123 A 1/22 123 B 1/13 123 C 1/22 123 A 1/24 123 A 1/11 123 B 1/10 123
SQL Query to use a Case Statement within and Aggregate Function
I have a query below that I need to get a count where Incident Records = 0. The first level is a summary of the records it grabs to calculate totals. I tried using COUNT(INCIDENTS!=’0′) but it apparently is not working though it does not error out. How would I get this count? This is in Snowflake …
Using SELECT subquery in a CASE statement and handling NULLS
I have 2 SQL tables one holding stock information then a style table which details how the stock is used. For some jobs the information is contained with a print file so the value in the stock table would be BIN, if not it would contain a template name which would cause a query to a Styles table which then
How to split an XML string into parts with different keys
from XML column I wanna try get info. Every client have more that one contract_key in XML, im using split_part and strpos for get needed information : But problem is this function return the first match found: ID Key amountR AVGamount 2253545 1 10000 50 this client in same XML have more Contract_key: ID Key a…
How to count how many time some value appeard with defined ID in PROC SQL in SAS Enterprise Guide?
I have Table in SAS Enterprise Guide like below: Data type: ID – numeric SERVICE – character ID SERVICE 123 P1 123 P1 123 G 444 AB 56 c2 And I need to know how many time each ID bought each SERVICE, so as a result I need something like below: Because for example ID = 123 bought SERVICE =
String length mismatch not giving data in another column
I’ve a column location in my table. I’ve used it as a common column to join two tables 1.Factory 2.Inventory Like Factory.location = inventory.location But I’m having a problem.. For example Location in factory table just has 09 where inventory table has 009 doesn’t match but for three…
SQL with grouping sets returns the initial table
I have a sample table like this: name manager country position salary Mike Mark USA Content Writer 40000 Kate Mark France SEO Specialist 12000 John Caroline USA Outreach Expert 32000 Alice Caroline Italy SEO Specialist 50000 Philip Caroline Italy Marketing Manager 30000 Julia Caroline Italy SEO Specialist 440…
MySQL – How to use JSON_EXTRACT to get values between two indices
I am trying to extract values between 2 indices in a JSON array using mysql JSON_EXTRACT. This query will smoothly return 20 as result. But if I want to get all the numbers between, say, 1st and 3rd indices, how do I query it? I was expecting something like: which will return 20,30,40. But not working. How do…