SQL 选择列的一部分
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13253445/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select Part of Column
提问by dawsonz
I was wondering if anyone could help with a query to select part of a column.
我想知道是否有人可以帮助查询选择列的一部分。
The column 'criteriadata' contains data that would look like this:
'criteriadata' 列包含如下所示的数据：
CriteriaData
14 27 15 C
14 30 15 DD
14 38 15 Pass
14 33 15 Pass
标准数据
14 27 15 摄氏度
14 30 15 日
14 38 15 通过
14 33 15 通过
How can I select just the data that appears after the number 15.
如何只选择出现在数字 15 之后的数据。
Many thanks.
非常感谢。
回答by Mahmoud Gamal
SELECT RIGHT(CriteriaData,
LEN(CriteriaData) - CHARINDEX('15', CriteriaData, 1) - 2)
FROM TableName
WHERE CriteriaData LIKE '%15%';
SQL Fiddle Demo
SQL 小提琴演示
回答by Mikael Eriksson
declare @T table
(
CriteriaData varchar(20)
)
insert into @T values
('14 27 15 C'),
('14 30 15 DD'),
('14 38 15 Pass'),
('14 33 15 Pass')
select stuff(CriteriaData, 1, 3+charindex(' 15 ', CriteriaData), '')
from @T
Result:
结果：
---------
C
DD
Pass
Pass
回答by ThierryT
If CriteriaCDataalways contains a pattern of 3 numbers of 2 numerics separated by a space then you always want to retrieve from 10th chars:
如果CriteriaCData总是包含由空格分隔的 2 个数字的 3 个数字的模式，那么您总是希望从第 10 个字符中检索：
select SUBSTR(CriteriaCData, 10) from xxx
If you are under oracle min 10.g then use REGEXP_SUBSTR to retrieve the alpha pattern
如果您在 oracle min 10.g 下，则使用 REGEXP_SUBSTR 来检索 alpha 模式
SELECT upper(REGEXP_SUBSTR(CriteriaCData, '[a-zA-Z]*$')) FROM xxx
回答by Jodrell
回答by r.karnati
On oracle use LENGTH instead of LEN
在 oracle 上使用 LENGTH 而不是 LEN
SELECT substr(CriteriaData, 8, LENGTH(CriteriaData) - 9) from table
回答by Richard Griffiths
As I had trouble making prior answers work, I had to find my own and figure for future reference I'd leave it on Stack Overflow. My field has XML but it's an NVarchar field and should generalise just fine - if you have a clear criteria for left AND right surrounding strings.
由于我无法使先前的答案起作用，因此我必须找到自己的答案以供将来参考，因此我将其留在 Stack Overflow 上。我的字段有 XML，但它是一个 NVarchar 字段，应该可以很好地概括 - 如果您对左右字符串有明确的标准。
It's not a complete match to this question but I hope it helps someone else who has huge strings in their columns and needs to snip out a string that varies in between two others!
这不是这个问题的完全匹配，但我希望它可以帮助其他人在他们的列中有很大的字符串并且需要剪掉一个在其他两个之间变化的字符串！
WITH r
AS (
SELECT TOP 100 RIGHT(XMLData, LEN(XMLData)-CHARINDEX('<INVOICE_NO>', XMLData)-11) AS xmldata
FROM IncomingPartsInvoiceXML)
SELECT LEFT(xmldata, CHARINDEX('<\/INVOICE_NO>', XMLData)-1)
FROM r;
回答by Clarice Bouwer
I created a SQL function to split the criteria by the spaces and used the last remaining value after the last space.
我创建了一个 SQL 函数来按空格分割条件，并使用最后一个空格后的最后一个剩余值。
create function dbo.getCriteria
(
@criteria varchar(500)
)
returns varchar(500)
begin
declare @space as int
select @space=charindex(' ', data) from mydata
while @space > 0
begin
set @criteria=substring(@criteria, @space + 1, len(@criteria))
select @space=charindex(' ', @criteria)
end
return @criteria
end
select dbo.getCriteria(data) from mydata
回答by Manikandan Sethuraju
Declare @x nvarchar(100) = '14 30 15 DD';
Select substring(@x, (select charindex('15',@x,1) + 2) ,len(@x));
回答by Johnno Nolan
SELECT substring(criteriadata, 9, LEN(criteriadata)-8) from table
SELECT substring(criteriadata, 9, LEN(criteriadata)-8) from table
This assumes that the position of 15 is fixed.
这假设 15 的位置是固定的。
回答by Ali Issa
You should use substring with left functions
您应该使用带有左函数的子字符串
Have a look at this: How to extract this specific substring in SQL Server?
看看这个：How to extract this specific substring in SQL Server?
And this: http://msdn.microsoft.com/en-us/library/aa259342(v=sql.80).aspx
而这个：http: //msdn.microsoft.com/en-us/library/aa259342(v=sql.80).aspx
