如何通过存储过程中的字符串模式获取表的列 [英] how do I get columns of table by patter of string in stored procedure

问题描述

我正在开发需要从表中匹配值的存储过程。例如，我表中的帐户列可以有2.1,2.1.1,2.1.2,2.2,3.4.5.7之类的值（数字用。分隔）现在我想从这个表中获取值基于图案。例。对于获取值2.1和2.2，我使用Sql查询select * from table where where like'2.％'..此查询获取2.1,2.2但2.1.1,2.1.2我不需要..

什么是Sql查询只能获取值2.1和2.2。

任何帮助!!

提前谢谢。

解决方案

实际上，没有一个简单的查询可以做到这一点：SQL字符串处理最多是基本的。 '％'匹配任意数量的字符（包括零），虽然有匹配任何一个字符：下划线（我无法键入，因为它触发Markdown到italice模式而不是显示它 - 这是一个站点错误）它总是匹配一个且只有一个字符。

所以 LIKE'2 ._'将解决当前问题并匹配2.1并且2.2没有匹配2.1.1 - 但它也找不到2.11。



我强烈建议你需要这样做您的演示文稿软件，或更改您的数据库设计，使SQL更容易处理这些东西。

您可以这样做 - 首先看一下：将列中的逗号分隔数据转换为行以供选择 [ ^ ]其中SE对CSV格式数据进行分类，并且可以很容易地对您的句点分隔数据进行修改 - 但是您需要弄清楚如何使用它来限制您的选择。不是一件容易的工作，而且在大桌子上效率很低！



我花了一些时间查看数据并查看是否有更好的存储方式无论现在还是将来，这种查询都会更容易。

您可以匹配单个字符，例如

从[table]中选择* WHERE帐户，如'2 ._'

或者你可以在where子句中包含你不想要的模式匹配例如

 选择 * 来自 [表]  WHERE 帐户喜欢 '  2.％' 
  和帐户不 喜欢 '  2。 ％。％'  

你可以用 PATINDEX [ ^ ] - 博客 [ ^ ]通过各种选项

I am developing stored procedure that requires matching value from table. For instance,account column in my table can have values like 2.1, 2.1.1, 2.1.2, 2.2, 3.4.5.7 and so on ( digit being separated by ".") Now I want to fetch value from this table based on pattern. Example. For fetching value 2.1 and 2.2, I used Sql query "select * from table where account like '2.%'".. this query fetched 2.1, 2.2 but also 2.1.1, 2.1.2 which I do not need..
What is the Sql query to fetch only value 2.1 and 2.2 .
Any help!!
Thanks in advance.

解决方案

In practice, there isn't a simple query that will do that: SQL string handling is basic at best. '%' matches any number of characters (including zero) and while there is a "match any one character": underscore (which I can't type as it triggers Markdown to "italice mode" instead of displaying it - it's a site bug) it will always match one and only one character.
So LIKE '2._' would solve the immediate problem and match "2.1" and "2.2" without matching "2.1.1" - but it won't find "2.11" either.

I'd strongly suggest that you need to do this in your presentation software, or change your DB design to make life easier for SQL to process this stuff.
You can probably do it - start by looking at this: Converting comma separated data in a column to rows for selection[^] which separates CSV style data and woudl be easy to mod to your period separated data - but then you need to work out how to use that to "limit" your selections. Not an easy job, and seriously inefficient on big tables!

I'd spend some time looking at the data and seeing if there is a better way to store it which makes this kind of query easier, now and in the future.

You can match on a single character e.g.

select * from [table] WHERE account like '2._'

or you can include in the where clause the pattern you don't want to match e.g.

select * from [table] WHERE account like '2.%'
and account not like '2.%.%'

You can do much more with PATINDEX[^] - this blog[^] goes through the various options

这篇关于如何通过存储过程中的字符串模式获取表的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！