如何从SQL Server中的varchar列中提取名称？ [英] How to extract names from varchar column in SQL Server ?
问题描述
这是一个在SQL中做的简单测试，我找不到任何解决方案来做到这一点。
我有一个下面的表名摘要，用户
+ ---- + ---------- -------------------------------------------------- -------------------------------- +
| id |摘要|
+ ---- + ---------------------------------------- -------------------------------------------------- - +
| 1 | asdffgggggg Anand * edkkofffmfmmfmfm Bala sdkdodkekeke Chandra dkkdkd vinoth * |
| 2 | asdffgggggg Dinesh * edkkofffmfmmfmfm Frankin sdkdodkekeke Elisia dkkdkd Ganesh。 |
| 3 | asdffgggggg Hansika edkkofffmfmmfmfm [A.Ishwariya] * sdkdodkekeke Jack dkkdkd Lalitha |
+ ---- + ---------------------------------------- -------------------------------------------------- - +
+ ---- + ------------- +
| id |名字|
+ ---- + ------------- +
| 1 | A.Ishwariya |
| 2 |阿南德|
| 3 |巴拉|
| 4 |钱德拉|
| 5 | Dinesh |
| 6 | Elisia |
| 7 |弗兰金|
| 8 | Ganesh |
| 9 | Hansika |
| 10 |杰克|
| 11 | Lalitha |
| 12 | Vinoth |
+ ---- + ------------- +
我希望摘要列中的所有名称都以*
输出1：
╔════════════════════b $ b║id║name║
╠════╬ ═════════════b $ b║ 1 ║Anand║
║ 1 ║Vinoth║
║ 2 ║Dinesh║
║ 3 ║A.Ishwariya║
╚═══════════════<
我希望汇总列中的所有名称都没有*
输出2：
╔════════════════b $ b║id║name║$ b $b╠═ ═══╬═══════════b $ b 1 ║Bala║
║ 1 ║钱德拉║
║ 2 ║Frankin║
║ 2 ║Elisia║
║ 2 ║Ganesh║
║ 3 ║Hansika║
║ 3 ║杰克║
║ 3 ║Lalitha║
╚═══════════════
我们非常感谢任何帮助。
解决方案
提供您的数据是固定的，所以名称总是在同一个位置，这非常简单：SQL有一个 SUBSTRING函数 [ ^ ]你可以使用它在选择中：
SELECT SUBSTRING（MyColumn， 10 ， 15 ） FROM MyTable WHERE MyColumn LIKE ' ％*'
但是...在你的情况下，我认为你会更好，更好地改变你的数据库设计，并将摘要信息拆分成一个单独的表SummaryID，前缀文本，名称和使用连接生成你想要的选择。尝试在SQL中对可行数据进行子字符串提取并不简单，并且很可能在很多条件下都会失败。使用具有更灵活的字符串处理功能的适当编程语言执行此操作要简单得多，并且从长远来看更可靠。
尝试使用以下查询：
查询带星号的记录
SELECT usr.id，用户 .name FROM 用户 usr
INNER JOIN 摘要smr
ON usr.id = smr.id
WHERE smr.summary LIKE ' ％*'
查询以获取没有星标的记录
SELECT usr.id， user .name FROM 用户 usr
INNER JOIN 摘要smr
ON usr.id = smr.id
WHERE smr .summary LIKE ' ％[^ *]'
1 。查询 获取记录 *
选择 id，从用户名称 u inner 加入摘要s
u.id = s.id
其中摘要喜欢 ' ％*'
2 。查询 获取记录，不含*
选择 id，name 来自用户u 内部 加入摘要s
< span class =code-keyword> on u.id = s.id
其中摘要不 喜欢 ' ％*'< /跨度>
It is a simple test to do in SQL, I cant find any solution to do this.
I have a below table name summary, user
+----+--------------------------------------------------------------------------------------------+
| id | summary |
+----+--------------------------------------------------------------------------------------------+
| 1 | asdffgggggg Anand * edkkofffmfmmfmfm Bala sdkdodkekeke Chandra dkkdkd "vinoth"* |
| 2 | asdffgggggg Dinesh * edkkofffmfmmfmfm Frankin sdkdodkekeke Elisia dkkdkd Ganesh. |
| 3 | asdffgggggg Hansika edkkofffmfmmfmfm [A.Ishwariya]* sdkdodkekeke Jack dkkdkd "Lalitha" |
+----+--------------------------------------------------------------------------------------------+
+----+-------------+
| id | name |
+----+-------------+
| 1 | A.Ishwariya |
| 2 | Anand |
| 3 | Bala |
| 4 | Chandra |
| 5 | Dinesh |
| 6 | Elisia |
| 7 | Frankin |
| 8 | Ganesh |
| 9 | Hansika |
| 10 | Jack |
| 11 | Lalitha |
| 12 | Vinoth |
+----+-------------+
I want to get all the names from the summary column ends with *
Output 1:
╔════╦═════════════╗
║ id ║ name ║
╠════╬═════════════╣
║ 1 ║ Anand ║
║ 1 ║ Vinoth ║
║ 2 ║ Dinesh ║
║ 3 ║ A.Ishwariya ║
╚════╩═════════════╝
I want to get all the names from the summary column ends without *
Output 2:
╔════╦═════════╗
║ id ║ name ║
╠════╬═════════╣
║ 1 ║ Bala ║
║ 1 ║ Chandra ║
║ 2 ║ Frankin ║
║ 2 ║ Elisia ║
║ 2 ║ Ganesh ║
║ 3 ║ Hansika ║
║ 3 ║ Jack ║
║ 3 ║ Lalitha ║
╚════╩═════════╝
Any help will be much appreciated.
解决方案 Provided that your data is fixed, so the name is always in the same position it's pretty simple: SQL has a SUBSTRING function[^] and you can use it in a select:
SELECT SUBSTRING(MyColumn, 10, 15) FROM MyTable WHERE MyColumn LIKE '%*'
But...in your case, I think you would be a lot, lot better off changing your DB desgin, and splitting out the summary info into a separate table of "SummaryID", "prefix text", "Name" and using a join to generate the selects you want. Trying to do substring extraction of viable data in SQL is not simple, and is very, very likely to fail under a lot of conditions. Doing this in a "proper" programming language with more flexible string handling facilities is a lot simpler, and more reliable in the long run.
Try with below queries:
Query to Fetch records with star
SELECT usr.id, user.name FROM User usr
INNER JOIN Summary smr
ON usr.id = smr.id
WHERE smr.summary LIKE '%*'
Query to Fetch records without star
SELECT usr.id, user.name FROM User usr
INNER JOIN Summary smr
ON usr.id = smr.id
WHERE smr.summary LIKE '%[^*]'
1. Query for get record with *
select id,name from users u inner join summary s
on u.id=s.id
where summary like '%*'
2. Query for get record without *
select id,name from users u inner join summary s
on u.id=s.id
where summary not like '%*'
这篇关于如何从SQL Server中的varchar列中提取名称？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
