Select Part of Column

问题:

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

14 27 15 C

14 30 15 DD

14 38 15 Pass

14 33 15 Pass

How can I select just the data that appears after the number 15.

Many thanks.

回答1:

SELECT RIGHT(CriteriaData, 
             LEN(CriteriaData) - CHARINDEX('15', CriteriaData, 1) - 2)
FROM TableName
WHERE CriteriaData LIKE '%15%';

SQL Fiddle Demo

回答2:

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

回答3:

If CriteriaCData always contains a pattern of 3 numbers of 2 numerics separated by a space then you always want to retrieve from 10th chars:

select SUBSTR(CriteriaCData, 10) from xxx

If you are under oracle min 10.g then use REGEXP_SUBSTR to retrieve the alpha pattern

SELECT upper(REGEXP_SUBSTR(CriteriaCData, '[a-zA-Z]*$')) FROM xxx

回答4:

You should use substring with left functions

Have a look at this: How to extract this specific substring in SQL Server?

And this: http://msdn.microsoft.com/en-us/library/aa259342(v=sql.80).aspx

回答5:

Since you seem to want everything from the ninth character onwards, you could use RIGHT and LEN

SELECT right([CriteriaData], len([CriteriaData]) - 9)

However, you'd be better off normalizing your data so it was already in a seperate column.

回答6:

On oracle use LENGTH instead of LEN

SELECT substr(CriteriaData, 8, LENGTH(CriteriaData) - 9) from table

回答7:

SELECT substring(criteriadata, 9, LEN(criteriadata)-8) from table

This assumes that the position of 15 is fixed.

回答8:

Declare @x nvarchar(100) = '14 30 15 DD';    
Select substring(@x, (select charindex('15',@x,1) + 2) ,len(@x));

回答9:

I created a SQL function to split the criteria by the spaces and used the last remaining value after the last space.

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

回答10:

SELECT 
  RIGHT(CriteriaData, LEN(CriteriaData) - (CHARINDEX('15', CriteriaData, 1) - 2)) 
FROM 
  MyTable;

回答11:

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.

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;