Trimming a nvarchar param in SQL Server 2019-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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