时间戳

首页 > 解决方案 > Need to ensure a hyphen between string

sql-server - Need to ensure a hyphen between string

问题描述

I need to check the length of character in b/w Procurement and WW: -it should be greater than 1 and if space is present (Procurement ww), replace with hyphen(like Procurement-ww). -if no string is present between (like Procurementww), then add a hyphen in between (like Procurement-ww)

Input: 

 -------------------------
 Procurement-ww13'18
 -------------------- 
 Procurement ww13'18
 --------------------
 Procurementww12'18
 --------------------
 Procurement ww12'18 abc-as
 --------------------
 Procurement ww11'18 NON-VMI (copy 5551)
 --------------------

I have tried it using Replace and Stuff functions. But i am not able to get the result.

First Sol

 declare @versionid int = 123, @versionname varchar(50) = 'Procurement ww12''18'
 If (@versionname not like '%-%')
 SELECT STUFF(@versionname,12,1,'-')
 print @versionname

--Second sol

 declare @versionid int = 123, @versionname varchar(50) = 'Procurement  ww12''18'
 If (@versionname not like '%-%')
 SELECT replace(@versionname,'procurement ','Procurement-')
 print @versionname

Output:(Hyphen in between 'Procurement' and 'ww')

 -------------------------------
 Procurement-ww13'18
 --------------------
 Procurement-ww13'18
 --------------------
 Procurement-ww12'18
 --------------------
 Procurement-ww12'18 abc-as
 --------------------
 Procurement-ww11'18 NON-VMI (copy 5551)
 --------------------

标签： sql-servertsql

解决方案

您可以使用 aSUBSTRING从ww以后获取值并将其附加到硬编码Procurement-，仅当该值尚不存在Procurement-ww且至少ww存在 a 时。

DECLARE @Input TABLE (
    Input VARCHAR(100),
    Fixed VARCHAR(100))

INSERT INTO @Input (Input)
VALUES
    ('Procurement-ww13''18'),
    ('Procurement ww13''18'),
    ('Procurementww12''18'),
    ('Procurement ww12''18 abc-as'),
    ('Procurement ww11''18 NON-VMI (copy 5551)'),
    ('Procurement     ww11''18 NON-VMI (copy 5551)'),
    ('Procurement NON-VMI (copy 5551)')


UPDATE I SET
    Fixed = 'Procurement-'
        + SUBSTRING(
            I.Input,
            CHARINDEX('ww', I.Input),
            100)
FROM
    @Input AS I
WHERE
    I.Input NOT LIKE 'Procurement-ww%' AND
    I.Input LIKE 'Procurement%ww%'

SELECT
    I.Input,
    I.Fixed
FROM
    @Input AS I

结果：

Input                                           Fixed
Procurement-ww13'18                             NULL
Procurement ww13'18                             Procurement-ww13'18
Procurementww12'18                              Procurement-ww12'18
Procurement ww12'18 abc-as                      Procurement-ww12'18 abc-as
Procurement ww11'18 NON-VMI (copy 5551)         Procurement-ww11'18 NON-VMI (copy 5551)
Procurement     ww11'18 NON-VMI (copy 5551)     Procurement-ww11'18 NON-VMI (copy 5551)
Procurement NON-VMI (copy 5551)                 NULL

如果您在变量检查中需要它：

DECLARE @Input VARCHAR(100) = 'Procurement     ww11''18 NON-VMI (copy 5551)'

IF @Input NOT LIKE 'Procurement-ww%' AND @Input LIKE 'Procurement%ww%'
    SET @Input = 'Procurement-'
        + SUBSTRING(
            @Input,
            CHARINDEX('ww', @Input),
            100)

SELECT @Input

推荐阅读