在 SQL Server 2019 中修剪 nvarchar 参数

发布于 2025-01-13 08:20:29 字数 569 浏览 0 评论 0原文

我有一个存储过程，它将字符串发送到我的参数之一

@deliveryName [nvarchar](255)

有时交付名称可以是“交付示例 1”，有时可以是“交付方法 - 交付示例 1”，也可能是“其他内容 - 交付示例 1” “

在它到达存储过程之前无法对其进行编辑，有没有一种安全的方法可以纯粹在sql server MSSQL中修剪第一个“-”之前的所有内容？我说如果送货名称中有多个“-”，有没有安全的办法。例如“交付方法 - 交付示例 - 2”我只想让它输出“交付示例 - 2”

到目前为止我所看到的所有地方都说

CASE
    WHEN str LIKE ',%,' THEN SUBSTRING(str, 2, LEN(str)-2)
    WHEN str LIKE ',%'  THEN RIGHT(str, LEN(str)-1)
    WHEN str LIKE '%,'  THEN LEFT(str, LEN(str)-1)
    ELSE str
END

对每种可能性使用一个案例（类似这样的），但总是有新的方法每天，所以这很难跟上。

原文

I have a stored procedure that sends a string to one of my parameters

@deliveryName [nvarchar](255)

Sometimes the delivery name can be "Delivery example 1" and sometimes it can be "Delivery method - Delivery example 1", it could also be "something else - Delivery example 1"

This cant be edited before it hits the stored procedure, is there a safe way to trim everything before the first "-" purely in sql server MSSQL? I say is there a safe way in the case that there are multiple "-" in the delivery name. for example "Delivery method - Delivery example - 2" i would just want it to output "Delivery example - 2"

Everywhere ive looked so far is saying to use a case when (something like this)

CASE
    WHEN str LIKE ',%,' THEN SUBSTRING(str, 2, LEN(str)-2)
    WHEN str LIKE ',%'  THEN RIGHT(str, LEN(str)-1)
    WHEN str LIKE '%,'  THEN LEFT(str, LEN(str)-1)
    ELSE str
END

for every eventuality but there are always new methods daily so this would be hard to keep up with.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

弃爱 2025-01-20 08:20:30

看起来它只需要一个带有 CHARINDEX 或 PATINDEX 的 SUBSTRING 来获取第一个破折号的位置。

测试片段

声明 @deliveryName nvarchar(255);
set @deliveryName = '发货方式 - 发货示例 - 2';

设置@deliveryName = ltrim(substring(@deliveryName, charindex('-', @deliveryName)+1, len(@deliveryName)));

选择@deliveryName作为deliveryName；

<表类=“s-表”>
<标题>
送货名称

<正文>
交付示例 - 2

db<>fiddle 上的演示此处< /em>

Looks like it just needs a SUBSTRING with a CHARINDEX or PATINDEX to get the position of the first dash.

Test snippet

declare @deliveryName nvarchar(255);
set @deliveryName = 'Delivery method - Delivery example - 2';

set @deliveryName = ltrim(substring(@deliveryName, charindex('-', @deliveryName)+1, len(@deliveryName)));

select @deliveryName as deliveryName;

deliveryName
Delivery example - 2

deliveryName
Delivery example - 2

Demo on db<>fiddle here

回复收藏 0 原文

海拔太高太耀眼 2025-01-20 08:20:30

对于没有“-”的名称，您可以将“-”附加到 DeliveryName 变量中，获取第一次出现“-”的索引，并获取该索引右侧的字符串

SELECT RIGHT(DeliveryName, CHARINDEX('-', DeliveryName+'-')+2) FROM #Temp

CREATE TABLE #Temp ( DeliveryName NVARCHAR(255))

INSERT INTO #Temp VALUES ('Delivery example-1')
INSERT INTO #Temp VALUES ('Delivery method - Delivery example 1 ')
INSERT INTO #Temp VALUES (' something else - Delivery example 1')
INSERT INTO #Temp VALUES (' Delivery method - Delivery example - 2')

SELECT RIGHT(DeliveryName, CHARINDEX('-', DeliveryName+'-')+2) FROM #Temp

output
--------------------------------------------------------
(No column name)
Delivery example 1
Delivery example 1 
Delivery example 1 
Delivery example - 2
Delivery example-1

You could append a '-' to your DeliveryName variable for names that don't have a '-', get the index of first occurence of '-' and get the string to right of that index

SELECT RIGHT(DeliveryName, CHARINDEX('-', DeliveryName+'-')+2) FROM #Temp

CREATE TABLE #Temp ( DeliveryName NVARCHAR(255))

INSERT INTO #Temp VALUES ('Delivery example-1')
INSERT INTO #Temp VALUES ('Delivery method - Delivery example 1 ')
INSERT INTO #Temp VALUES (' something else - Delivery example 1')
INSERT INTO #Temp VALUES (' Delivery method - Delivery example - 2')

SELECT RIGHT(DeliveryName, CHARINDEX('-', DeliveryName+'-')+2) FROM #Temp

output
--------------------------------------------------------
(No column name)
Delivery example 1
Delivery example 1 
Delivery example 1 
Delivery example - 2
Delivery example-1

回复收藏 0 原文

~没有更多了~