从数据库字段中仅提取大写文本

发布于 2025-01-04 22:42:00 字数 177 浏览 1 评论 0原文

我“继承”了一个数据库，其中表中的一个字段有小写和大写混合在一起，例如。

gateway 71, HOWARD BLVD, Chispa, NY

它们不容易用代码“拆分”，因为它们并不总是以这种形式出现。我需要的是一种只提取大写字母的方法。这可以用 SQLite 实现吗？

原文

I "inherited" a db with a field in a table where there are lowercase and uppercase mixed together, eg.

gateway 71, HOWARD BLVD, Chispa, NY

They aren't easily "splittable" with code because they don't always come in this form. What I need is a way to extract only uppercase letters. Is this possible with SQLite?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

谁许谁一生繁华 2025-01-11 22:42:00

在这种情况下，使用您可能有的任何附加 WHERE 要求进行 SELECT 并创建游标来迭代在代码中进行大写检查的结果可能会更容易（并且可能更快）。

SQLite 的另一个选择是创建一个自定义函数，这样你就可以执行以下操作：

SELECT foo WHERE MYISALLUPPERFUNC(foo) = 1;

This is a case where it may just be easier (and perhaps quicker) to SELECT with any additional WHERE requirements you may have and create a cursor to iterate over the results doing your uppercase checks in code.

Another option with SQLite would be to create a custom function, so you could do something like:

SELECT foo WHERE MYISALLUPPERFUNC(foo) = 1;

回复收藏 0 原文

惟欲睡 2025-01-11 22:42:00

正如 NuSkooler 提到的，使用光标可能更容易、更快；如果您只需执行一次，这是一个特别有吸引力的选择。

这是一个简单的示例（使用 Python REPL 中的内置 SQLite）：

import sqlite3

with sqlite3.connect(":memory:") as conn:
    conn.execute('''create table t (c, newc);''')
    conn.commit()
    conn.execute('''insert into t (c) values (?);''', ('testing MAIN ST',))
    conn.commit() 
    results = conn.execute('select c from t;').fetchall()
    for line in results:
        tokens = line[0].split()
        filtered_tokens = [i for i in tokens if i.isupper()]
        newc = ' '.join(filtered_tokens)
        conn.execute('update t set newc = ?;',(newc,))
        conn.commit()

    conn.execute('''select c,newc from t;''').fetchone()
    # (u'testing MAIN ST', u'MAIN ST')

As NuSkooler mentions, this is probably easier and faster to do using a cursor; an especially attractive option if you will only have to do this once.

Here's a quick example (using built-in SQLite from Python REPL):

import sqlite3

with sqlite3.connect(":memory:") as conn:
    conn.execute('''create table t (c, newc);''')
    conn.commit()
    conn.execute('''insert into t (c) values (?);''', ('testing MAIN ST',))
    conn.commit() 
    results = conn.execute('select c from t;').fetchall()
    for line in results:
        tokens = line[0].split()
        filtered_tokens = [i for i in tokens if i.isupper()]
        newc = ' '.join(filtered_tokens)
        conn.execute('update t set newc = ?;',(newc,))
        conn.commit()

    conn.execute('''select c,newc from t;''').fetchone()
    # (u'testing MAIN ST', u'MAIN ST')

回复收藏 0 原文

~没有更多了~