gpt4 book ai didi

sqlite - 我想要只有字符(完全没有符号、数字和空格)

转载 作者:行者123 更新时间:2023-12-03 17:56:06 25 4
gpt4 key购买 nike

应该用 SQLite 完成

像这样;

enter image description here

是的,我知道,这很容易,如果我使用 UDF (用户定义函数)。

但是,我对此有很大的困难。

所以,寻找另一种方式( 没有 UDF 方式)来实现我的目标。

谢谢

供您引用,

我留下了一个我未能制作 UDF 的链接(使用 AutoHotkey)

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

最佳答案

我相信您可以将决议基于:-

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;

演示:-

也许考虑以下几点:-
/* 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;

这导致: -

enter image description here

关于sqlite - 我想要只有字符(完全没有符号、数字和空格),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/59453369/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com