截断 SQL 单元格中的特定字符数

Question

我在表中有Description列

Description 
---------------
AA Check 
B1 Check
RD/AA Check 
WD_FA Examine 
FF Examine 
AA Pass 
B2 Check 
Examine  

所需的输出

Description 
---------------
Check 
Examine  
Pass 

基本上是一个案例语句，它从单元格的左侧截断。请注意，该列表以不同的名称和条件继续，并且某些单元格不需要修改(如最后一行(，因此类似于 case 语句，但如果我未指定条件，则不返回 NULL。 谢谢

Answer 1

这会删除第一个空格以下的数据

case when x like '% %' 
then substring(x, charindex(' ', x) +1, 8000)
else x
end
'AA Examine Device' -> 'Examine Device'

这提取了最后一个词：

case when x like '% %' 
then right(x, charindex(' ', reverse(x)) -1)
else x
end
'AA Examine Device' -> 'Device'

Answer 2

在你拥有的空格之后，并使用DISTINCT，这将获取空格后面的最后一个单词，或者如果它是唯一的单词，则返回单词。

declare @Description table (Description varchar(64))
insert into @Description
values
('AA Check'),
('B1 Check'),
('RD/AA Check'),
('WD_FA Examine'),
('FF Examine'),
('AA Pass'),
('B2 Check'),
('Examine')
select distinct
right([Description],len([Description]) - charindex(' ',[Description]))
from @Description

Answer 3

你可以像下面这样做：

select distinct substring(Description, charindex(' ', Description) + 1, len(Description))

Answer 4

这个问题太宽泛了，但你可以使用类似的东西

DECLARE @Product TABLE (
[Description] nvarchar(255),
Condition int
);
INSERT @Product ([Description], Condition)
select 'AA Check', 1 union all
select 'B1 Check', 2 union all
select 'RD/AA Check', 3 union all
select 'WD_FA Examine', 3 union all
select 'FF Examine', 4 union all
select 'AA Pass', 1 union all
select 'B2 Check', 5 union all
select 'Examine', 6

select
[Description],
case 
when Condition = 1 then replace([Description],'AA ','')
when Condition = 2 then replace([Description],'B1 ','')
when Condition = 3 then ltrim(rtrim(right([Description],len([Description]) - 5)))
when Condition = 5 then replace([Description],'B2 ','')
when Condition = 6 then [Description]
else 
''
end as 'NewDescription'
from @Product

我希望它有所帮助 🙂

Answer 5

也许是这样的：

;WITH CTE (Column1) AS (
SELECT * FROM (
VALUES
('    AA Check '), ('    B1 Check'), ('    RD/AA Check '), ('    WD_FA Examine '), ('    FF Examine '), 
('    AA Pass '), ('    B2 Check '), ('    Examine  ')
) AS A (Column1)
)
SELECT 
CASE WHEN CHARINDEX('Check', Column1) > 0 THEN SUBSTRING(Column1, CHARINDEX('Check', Column1), 5)
WHEN CHARINDEX('Examine', Column1) > 0 THEN SUBSTRING(Column1, CHARINDEX('Examine', Column1), 7)
WHEN CHARINDEX('Pass', Column1) > 0 THEN SUBSTRING(Column1, CHARINDEX('Pass', Column1), 4)
END AS Results
FROM CTE

但是，如果您有很多变量，那将是痛苦的。

Answer 6

CREATE TABLE [A] ( [Description] VARCHAR(100));
INSERT [A]
VALUES
---------------
( 'AA Check' )
,( 'B1 Check' )
,( 'RD/AA Check' )
,( 'WD_FA Examine' )
,( 'FF Examine' )
,( 'AA Pass' )
,( 'B2 Check' )
,( 'Examine' )
,( 'Examine Device' );
SELECT DISTINCT
CASE
WHEN [Description] COLLATE Latin1_General_BIN LIKE '[A-Z][a-z]%' THEN
[Description]
WHEN [Description] COLLATE Latin1_General_BIN LIKE '[A-Z][A-Z0-9]%' THEN
SUBSTRING([Description], CHARINDEX(' ', [Description]) + 1, 8000)
END AS [Description]
FROM
[A];
DROP TABLE [A];