SQL中的三元运算符?“传递给 LEFT 或 SUBSTRING 函数的长度参数无效" [英] Ternary operator in SQL? "invalid length parameter passed to the LEFT or SUBSTRING function"

问题描述

对不起，这个误导性的主题，我不知道如何用词更好.因为我主要是一名软件开发人员，所以我想到了三元运算符，我遇到了以下问题.

Sorry for this misleading subject, i didn't know how to word better. Because i'm mainly a software-developer, the ternary operator comes to my mind with my following problem.

我需要找到通过可为空的外键(modModel 和 tabSparePart)链接两个表的最可靠方法.两者之间唯一的相似之处是模型的名称和备件的描述(tabSparePart 是来自客户的自动导入的外部表，因此这不是我的责任，我无法更改数据).

I need to find the most robust way to link two tables via nullable foreign-key(modModel and tabSparePart). The only similarity between both is the model's name and the sparepart's description(the tabSparePart is an external table from customer that is imported automatically, so it's not my responsibility and i cannot change the data).

考虑以下备件名称:

W200I_E/Swap
EXCHANGEUNIT P1i / SILVERBLACK/ CYRILLIC

我要查找的型号是 P1i 和 W200I_E.因此，我可以在 where-clause 中确保只有一个强有力的规则:

The modelnames that i want to find are P1i and W200I_E. So there is only one strong rule that i can ensure in the where-clause:

• 必须有一个分隔符 / 并且相关部分是第一个.

• there must be a separator / and the relevant part is the first one.

这是示例数据:

Create table #temp(Partname varchar(100))
INSERT INTO #temp
    SELECT 'EXCHANGEUNIT P1i / SILVERBLACK/ CYRILLIC' UNION ALL SELECT 'W200I_E/Swap unit/Black' 

我会完成以下查询:

SELECT RTRIM(LEFT(Partname, CHARINDEX('/', Partname) - 1)) AS UNIT
FROM #temp
WHERE CHARINDEX('/', Partname) > 0

...返回什么:

EXCHANGEUNIT P1i
W200I_E

但我需要P1i.所以我需要一种方法来处理第一部分由空格分隔的情况.在这种情况下，我需要选择最后一个单词，但前提是它被完全分开.

But i need P1i. So i need a way to handle also the case that the first part is separated by whitespaces. In that case i need to select the last word, but only if it is separated at all.

我收到一个 无效的长度参数传递给 LEFT 或 SUBSTRING 函数" - 错误与以下查询:

I'm getting a "invalid length parameter passed to the LEFT or SUBSTRING function"-error with following query:

SELECT REVERSE( LEFT( REVERSE(RTRIM(LEFT(Partname, CHARINDEX('/', Partname) - 1)))
    , CHARINDEX(' ', REVERSE(RTRIM(LEFT(Partname, CHARINDEX('/', Partname) - 1))))-1 ))
    AS Unit
FROM #temp
WHERE CHARINDEX('/', Partname) > 0

如果没有第二个没有空格的记录，这将起作用.如果我还要确保第一部分包含空格，我会丢弃有效记录.

This would work without the second record that has no whitespace. If i would also ensure that the first part contains a whitespace, i would discard valid records.

长话短说，我需要根据分隔符的存在找到一种将两种方式结合起来的方法.

To cut a long story short, I need to find a way to combine both ways according to the existence of separators.

PS:这源于:获取 varchar 一部分的最后一个字(左/右)

如果有人感兴趣，这是完整的(工作)存储过程.我确定我从来没有用过这么奇怪的 JOIN:

If anybody is interested, this is the complete (working) stored-procedure. I'm sure i've never used such a strange JOIN:

CREATE PROC [dbo].[UpdateModelSparePart](@updateCount int output)
with execute as Owner
AS
    BEGIN
    BEGIN TRANSACTION

    UPDATE modModel SET fiSparePart=ModelPart.idSparePart
        FROM modModel INNER JOIN 
        (
          SELECT m.idModel
                ,m.ModelName
                ,sp.idSparePart
                ,sp.Price
                ,Row_Number()Over(Partition By idModel ORDER BY Price DESC)as ModelPrice
          FROM modModel AS m INNER JOIN tabSparePart AS sp 
            ON m.ModelName = CASE 
                WHEN CHARINDEX(' ', REVERSE(RTRIM(LEFT(sp.SparePartDescription, CHARINDEX('/', sp.SparePartDescription) - 1))))  > 0 THEN
                    REVERSE(  LEFT( REVERSE(RTRIM(LEFT(sp.SparePartDescription, CHARINDEX('/', sp.SparePartDescription) - 1)))
                    ,CHARINDEX(' ', REVERSE(RTRIM(LEFT(sp.SparePartDescription, CHARINDEX('/', sp.SparePartDescription) - 1))))-1 ))
                ELSE
                    RTRIM(LEFT(sp.SparePartDescription, CHARINDEX('/', sp.SparePartDescription) - 1))
                END
          WHERE (CHARINDEX('/', sp.SparePartDescription) > 0)
          GROUP BY idModel,ModelName,idSparePart,Price
        )As ModelPart
    ON ModelPart.idModel=modModel.idModel
    Where ModelPrice=1

    SET @updateCount = @@ROWCOUNT;

    COMMIT TRANSACTION

END

推荐答案

我解决了这个问题:

SELECT 'Unit' =
    CASE 
    WHEN CHARINDEX(' ', REVERSE(RTRIM(LEFT(Partname, CHARINDEX('/', Partname) - 1))))  > 0 THEN
         REVERSE( LEFT( REVERSE(RTRIM(LEFT(Partname, CHARINDEX('/', Partname) - 1)))
        ,CHARINDEX(' ', REVERSE(RTRIM(LEFT(Partname, CHARINDEX('/', Partname) - 1))))-1 ))
    ELSE 
        RTRIM(LEFT(Partname, CHARINDEX('/', Partname) - 1))
    END
FROM #temp
WHERE CHARINDEX('/', Partname) > 0

丑陋但工作正常.

这篇关于SQL中的三元运算符?“传递给 LEFT 或 SUBSTRING 函数的长度参数无效"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！