CODEWITHSUNDEEP
sql-servert-sqlsplitupdatesstring-parsing
Ehsan Akbar
Ehsan Akbar

Reputation: 7299

Insert a portion of one string into another string in a given position

I have these columns in sql server 

LineNumber                           DocumentNumber
1.1/2-GM-100-10026-G01-N-1-1001      DW-2224-100-1330-1001

10-FW-130-13576-B90-N-1-100-1001     DW-2224-100-1330-1001

4-IA-160-12325-B10-N-1-2001          DW-2224-160-1330-2001

UG-12-CS-160-160CMH306-B93-N-1-1062  DW-2224-160-1433-1062

24-SL-160-12116-B12-H-1-160-MMP          DW-2224-160-1433-1062

I want to update linenumber base on documentnumber .I want to just add third part of documentnumber i mean e.x"100,160,100 and etc" add to my line number before last part i mean 2001,1001 the result i expected is :

LineNumber                                    DocumentNumber
1.1/2-GM-100-10026-G01-N-1-100-1001           DW-2224-100-1330-1001

10-FW-130-13576-B90-N-1-100-1001              DW-2224-100-1330-1001

4-IA-160-12325-B10-N-1-160-2001               DW-2224-160-1330-2001

UG-12-CS-160-160CMH306-B93-N-1-160-1062       DW-2224-160-1433-1062

24-SL-160-12116-B12-H-1-160-MMP               DW-2224-160-1433-1062

The third part of docnumber added to linenumber .but some linenumber has this part and it shouldn't be added .

I want to update it in sql server .

\SELECT  [Id]
      ,[LineNumber]
      ,[DocumentNumber]

  FROM [SPMS2].[dbo].[Lines]

UPDATE

This is the result of @Shnugo's answer. But - as you see - the part exists already in some cases. After the inserts this part is doubled, but I need them only once!

enter image description here

Upvotes: 1

Views: 62

Answers (3)

Srini
Srini

Reputation: 51

CREATE TABLE [dbo].[Doc](
       [id] [int] IDENTITY(1,1) NOT NULL,
       [LineNumber] [varchar](200) NULL,
       [DocumentNumber] [varchar](200) NULL
) ON [PRIMARY]

GO
INSERT INTO [Doc] ([LineNumber],[DocumentNumber])VALUES('1.1/2-GM-100-10026-G01-N-1-1001','DW-2224-100-1330-1001')
INSERT INTO [Doc] ([LineNumber],[DocumentNumber])VALUES('10-FW-130-13576-B90-N-1-100-1001','DW-2224-100-1330-1001')
INSERT INTO [Doc] ([LineNumber],[DocumentNumber])VALUES('4-IA-160-12325-B10-N-1-2001','DW-2224-160-1330-2001')
INSERT INTO [Doc] ([LineNumber],[DocumentNumber])VALUES('UG-12-CS-160-160CMH306-B93-N-1-1062','DW-2224-160-1433-1062')
INSERT INTO [Doc] ([LineNumber],[DocumentNumber])VALUES('24-SL-160-12116-B12-H-1-160-MMP','DW-2224-160-1433-1062')
GO
SELECT
  Id,
  DocumentNumber,
  LineNumber,
  REVERSE(STUFF(REVERSE(LineNumber), CHARINDEX('-', REVERSE(LineNumber)) + 1, 0, REVERSE(REVERSE(REVERSE('-' + SUBSTRING(DocumentNumber, 9, LEN(DocumentNumber) - 18)))))) 
              AS LineNumberInRequiredFormat
FROM [Doc]


    Result
    LineNumberInRequiredFormat
    *********************************
    1.1/2-GM-100-10026-G01-N-1-100-1001
    10-FW-130-13576-B90-N-1-100-100-1001
    4-IA-160-12325-B10-N-1-160-2001
    UG-12-CS-160-160CMH306-B93-N-1-160-1062
    24-SL-160-12116-B12-H-1-160-160-MMP

Upvotes: 1

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14679

However solution given by @Shnugo is good one.

Still you can try below option.

Create one function which split data by given character as below:

CREATE FUNCTION [dbo].[fnSplit]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)

Now, Use below select statement

SELECT REPLACE
(
    LineNumber,
    '-'+ REVERSE(Substring(REVERSE(LineNumber),0, charindex('-',REVERSE(LineNumber)))),
    '-'+
    (
        SELECT Data FROM [dbo].[fnSplit] (DocumentNumber,'-') Where Id=3
    )+'-'+ REVERSE(Substring(REVERSE(LineNumber),0, charindex('-',REVERSE(LineNumber))))
)
FROM TableName

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Sorry, this design is horrible... If you need such a string it should be built in the second you need it according to some business rules out of the correct parts. You should really normalize this!

But if you have to stick to it, try it like this:

DECLARE @tbl TABLE(LineNumber VARCHAR(100),DocumentNumber VARCHAR(100));
INSERT INTO @tbl VALUES
 ('1.1/2-GM-100-10026-G01-N-1-1001','DW-2224-100-1330-1001')
,('10-FW-130-13576-B90-N-1-100-1001','DW-2224-100-1330-1001')
,('4-IA-160-12325-B10-N-1-2001','DW-2224-160-1330-2001')
,('UG-12-CS-160-160CMH306-B93-N-1-1062','DW-2224-160-1433-1062')
,('24-SL-160-12116-B12-H-1-160-MMP','DW-2224-160-1433-1062');

SELECT STUFF(LineNumber
             ,LEN(LineNumber)-CHARINDEX('-',REVERSE(LineNumber)) + 1
             ,0
             ,'-' + CAST('<x>' + REPLACE(DocumentNumber,'-','</x><x>') + '</x>' AS XML).value('/x[3]','varchar(max)'))
FROM @tbl AS tbl

This is the result

1.1/2-GM-100-10026-G01-N-1-100-1001
10-FW-130-13576-B90-N-1-100-100-1001
4-IA-160-12325-B10-N-1-160-2001
UG-12-CS-160-160CMH306-B93-N-1-160-1062
24-SL-160-12116-B12-H-1-160-160-MMP     --Here the 160 is twice due to the test data...

The cast to XML allows to select the third part of your DocumentNumber easily (but be aware of forbidden characters like <, > and & and some more). The STUFF function puts this string in the right position (the first hyphen in the reversed string)

UPDATE

According to your comment, this new query will first extract both parts and compare them. The insert happens only, if the second last part in line number is not equal to the third part in DocumentNumber

SELECT CASE WHEN SecondLastInLineNumber=ThirdInDocumentNumber THEN LineNumber
       ELSE
        STUFF(LineNumber
             ,LEN(LineNumber)-CHARINDEX('-',REVERSE(LineNumber)) + 1
             ,0
             ,'-' + ThirdInDocumentNumber)
       END AS NewLineNumber
FROM @tbl AS tbl
CROSS APPLY
(
    SELECT REVERSE(CAST('<x>' + REPLACE(REVERSE(LineNumber),'-','</x><x>') + '</x>' AS XML).value('/x[2]','varchar(max)'))
          ,CAST('<x>' + REPLACE(DocumentNumber,'-','</x><x>') + '</x>' AS XML).value('/x[3]','varchar(max)')
) AS Parts(SecondLastInLineNumber,ThirdInDocumentNumber)

The result

1.1/2-GM-100-10026-G01-N-1-100-1001
10-FW-130-13576-B90-N-1-100-1001
4-IA-160-12325-B10-N-1-160-2001
UG-12-CS-160-160CMH306-B93-N-1-160-1062
24-SL-160-12116-B12-H-1-160-MMP

Upvotes: 2

Related Questions