MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL and JavaScript

without comments

Sometimes students truly amaze me with their curiosity. The question was simple from their perspective while we were discussing MySQL’s builtin string functions. How would you do something like this JavaScript logic without using literals or session variables?

// Declare a string and substring.
var myStr = 'Get me from the string.'
var mySubstr = 'me'
 
// Assign the substring to variable by rescuing it from the larger string.
var rescued = myStr.substring(myStr.indexOf(mySubstr),myStr.indexOf(mySubstr) + mySubstr.length)
 
// Print the result.
print(rescued)

tested with MongoDB, like

mongo --nodb --norc < parsing.js

returning:

MongoDB shell version v4.0.20
me
bye

They thought the question would show SQL’s limits as a problem solving and programming language because they didn’t see how MySQL could assign a variable for evaluation in the builtin functions.

They were surprised to see how I showed them that they could do it. Since they disallowed session variables, I built a sample table and inserted the string value in a text column before writing a query with a Common Table Expression (CTE).

The MySQL steps are:

-- Stable testing scenario with table values requires a test table.
DROP TABLE IF EXISTS sample;
CREATE TABLE sample
( text  VARCHAR(100) );
 
-- Insert the literal string into the testing table.
INSERT INTO sample
( text )
VALUES
('Get me from the string.');
 
-- Test using a WITH clause to place a variable in context for use
-- in the query, relying on the fact that a Cartesian set of one
-- column and row becomes a new column in all rows of the other
-- table's set.
WITH struct AS
(SELECT 'me' AS result)
 SELECT SUBSTR(text,INSTR(text,struct.result),LENGTH(struct.result)) AS rescued
 FROM   sample CROSS JOIN struct;

It returns the following:

+---------+
| rescued |
+---------+
| me      |
+---------+
1 row in set (0.00 sec)

Wow, SQL works like a programming language was the response of the class. It’s like anything else in technology, new stuff isn’t as cool as old stuff until you learn how to use it.

Written by maclochlainn

October 24th, 2021 at 10:48 am