时间戳

首页 > 解决方案 > SQL：我如何正确区分字符串中的所有单词，除非它们是 <= 3 字符

sql - SQL：我如何正确区分字符串中的所有单词，除非它们是 <= 3 字符

问题描述

我正在寻找 SQL 函数，它将查看字符串和每个单词的正确大小写，除非它是 <= 3 个字符......在这种情况下，它需要全部大写

例如：

2021 雪佛兰西尔维拉多 Ltz

会成为

2021雪佛兰西尔维拉多LTZ

2020 本田思域 LX 轿车

会成为

2020本田思域LX轿车

我编写了一个函数来查看整个字符串及其长度，我只是不确定如何查看每个项目一旦被空格分隔。这是我现在拥有的功能。

ALTER FUNCTION [dbo].[ucwords] (@InputString VARCHAR(4000), @wordsLongerThan INT = -1 )
RETURNS VARCHAR(4000)
AS
BEGIN
IF @InputString IS NULL
    RETURN NULL;

IF (@wordsLongerThan <> -1 AND LEN(@InputString) < @wordsLongerThan)
    return @InputString;

DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @OutputString VARCHAR(255)

SET @OutputString = LOWER(@InputString)
SET @Index = 2
SET @OutputString = STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))

WHILE @Index <= LEN(@InputString)
BEGIN
    SET @Char = SUBSTRING(@InputString, @Index, 1)
    IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''','(')
    IF @Index + 1 <= LEN(@InputString)
BEGIN
    IF @Char != ''''
    OR
    UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
    SET @OutputString =
    STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
END
    SET @Index = @Index + 1
END

RETURN ISNULL(@OutputString,'')
END

标签： sqlsql-serveruppercase

解决方案

鉴于您使用的是 SQL Server 2016，请尝试以下操作：

CREATE OR ALTER FUNCTION dbo.ucwords ( 
    @Sentence varchar(4000)
)
RETURNS varchar(4000)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN

    DECLARE @Cased varchar(4000) = '';  --<-- initializing to an empty string is important.

    SELECT
        @Cased = @Cased + ' ' + CASE 
            WHEN LEN ( Word ) <= 3 THEN UPPER ( Word )
            ELSE UPPER ( LEFT ( Word, 1 ) ) + LOWER ( RIGHT ( Word, LEN ( Word ) - 1 ) )
        END
    FROM ( 
        
        SELECT value AS Word FROM STRING_SPLIT ( @Sentence, ' ' )

    ) AS Words;

    RETURN LTRIM ( @Cased );

END
GO

然后尝试：

SELECT dbo.ucwords ( '2020 HONDA CIVIC LX SEDAN' ) AS [ucwords];

退货

+---------------------------+
|          ucwords          |
+---------------------------+
| 2020 Honda Civic LX Sedan |
+---------------------------+

也试试：

SELECT dbo.ucwords ( '2021 CHEVROLET Silverado Ltz' ) AS [ucwords];

退货

+------------------------------+
|           ucwords            |
+------------------------------+
| 2021 Chevrolet Silverado LTZ |
+------------------------------+

推荐阅读