2

It should be done with SQLite

just like this;

enter image description here

yes, I know, it is quite easy task, If I use UDF(User Define Function).

but, I have severe difficulty with it.

so, looking for another way (no UDF way) to achieve my goal.

Thanks

for your reference,

I leave a link that I have failed to make UDF (using AutoHotkey)

SQLite/AutoHotkey, I have problem with Encoding of sqlite3_result_text return function

Kita Nagoya
  • 100
  • 6

1 Answers1

1

I believe that you could base the resolution on :-

WITH RECURSIVE eachchar(counter,rowid,c,rest) AS (
        SELECT 1,rowid,'',mycolumn AS rest FROM mytable
        UNION ALL 
            SELECT counter+1,rowid,substr(rest,1,1),substr(rest,2) FROM eachchar WHERE length(rest) > 0 LIMIT 100
    )
SELECT group_concat(c,'') AS mycolumn, myothercolumn, mycolumn AS original
FROM eachchar JOIN mytable ON eachchar.rowid = mytable.rowid 
WHERE length(c) > 0 
    AND (
        unicode(c) BETWEEN unicode('a') AND unicode('z') 
        OR unicode(c) BETWEEN unicode('A') AND unicode('Z')
    )
GROUP BY rowid;

Demo :-

Perhaps consider the following :-

/* Create the Test Environment */
DROP TABLE IF EXISTS mytable;
CREATE TABLE IF NOT EXISTS mytable (mycolumn TEXT, myothercolumn);
/* Add the Testing data */
INSERT INTO mytable VALUES
    ('123-abc_"D E F()[]{}~`!@#$%^&*-+=|\?><<:;''','A')
    ,('123-xyz_"X Y Z()[]{}~`!@#$%^&*-+=|\?><<:;''','B')
    ,('123-abc_"A B C()[]{}~`!@#$%^&*-+=|\?><<:;''','C')
;

/* split each character thenconcatenat only the required characters*/
WITH RECURSIVE eachchar(counter,rowid,c,rest) AS (
        SELECT 1,rowid,'',mycolumn AS rest FROM mytable
        UNION ALL 
            SELECT counter+1,rowid,substr(rest,1,1),substr(rest,2) FROM eachchar WHERE length(rest) > 0 LIMIT 100
    )
SELECT group_concat(c,'') AS mycolumn, myothercolumn, mycolumn AS original
FROM eachchar JOIN mytable ON eachchar.rowid = mytable.rowid 
WHERE length(c) > 0 
    AND (
        unicode(c) BETWEEN unicode('a') AND unicode('z') 
        OR unicode(c) BETWEEN unicode('A') AND unicode('Z')
    )
GROUP BY rowid;
/* Cleanup Test Environment */
DROP TABLE IF EXISTS mytable;

This results in :-

enter image description here

MikeT
  • 51,415
  • 16
  • 49
  • 68
  • Wow.. looks quite advanced (I am a beginner). Thanks Lot, I will take a look at it and execute it for sure with time. – Kita Nagoya Dec 23 '19 at 12:01
  • @KitaNagoya you might want to have a look at [The Simplest SQLite Common Table Expression Tutorial](https://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/). Oh and the `LIMIT 1000` was a *just in case* limiter, so you may want to increase it or do away with it as the `length(rest)` should be sufficient to stop the recursion. – MikeT Dec 23 '19 at 12:11
  • Yap, I am a beginner so, your link sould be very good guide for my long and painful journey. Thanks again. – Kita Nagoya Dec 23 '19 at 13:38