How to escape a ' character in the SQL update with replace function?

Question

I have this data in my table

  PartNumber  | IsValid
 BC.QT.000002'     0
'CP.AC.'000010'    0
'CP.AL.000013      0
'CP.A'L.000016'    0
'CP.AL.000024'     0

What I am trying to do is to remove the ' from PartNumber value, but if that quote ' is in the middle of the value for example 'CP.AC.'000010' I only want to remove the suffix and the prefix '

The desired result would look something like this

 PartNumber | IsValid
BC.QT.000002     0
CP.AC.'000010    0
CP.AL.000013     0
CP.A'L.000016    0
CP.AL.000024     0

Here is the SQL that I tried

CREATE TABLE #Temp(
   PartNumber          VARCHAR(20)
  ,Test                VARCHAR(30)
);
INSERT INTO #Temp(PartNumber,Test) VALUES ('''test''',NULL);
INSERT INTO #Temp(PartNumber,Test) VALUES ('''te''st2''',NULL);
INSERT INTO #Temp(PartNumber,Test) VALUES ('''test3''',NULL);
INSERT INTO #Temp(PartNumber,Test) VALUES ('''tes''t5''',NULL);

Update #Temp
set partNumber = REPLACE(PartNumber,'''','')
Where len(partNumber ) - len(replace(partNumber , '''', '')) = 2

This does not work because it completely ignores values where the there is 3 ' in the value, or if there are 2 ' but one of them in the middle.

I need a way to remove the prefix/suffix ' if they exist.

Gordon Linoff · Accepted Answer · 2018-09-13 21:08:46Z

3

Assuming you don't have spaces in PartNumber, you can use:

replace(ltrim(rtrim(replace(partnumber, '''', ' '))), ' ', '''')

This handles removing the characters from the beginning and end of the string by using the trim functions. Alas, these only work on spaces; so replace is needed to turn the quotes into spaces.

answered Sep 13, 2018 at 21:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zohar Peled · Accepted Answer · 2018-09-13 21:21:40Z

Another option, that would be safe even if the content may contain spaces, would be to use case:

UPDATE #Temp
SET partNumber = CASE WHEN partNumber LIKE '''%''' THEN -- starts and ends with an apostrophe
                    SUBSTRING(partNumber, 2, LEN(partNumber)-2) 
                 WHEN partNumber LIKE '''%' THEN -- starts with an apostrophe
                     RIGHT(partNumber, LEN(partNumber)-1) 
                 WHEN partNumber LIKE '%''' THEN -- ends with an apostrophe
                     LEFT(partNumber, LEN(partNumber)-1) 
                 ELSE  
                     partNumber 
                 END

The downside, other than being more cumbersome than Gordon's answer, is that it will only remove the first and last apostrophes - if you have a value like ''asdf', it will be updated as 'asdf.

Hector Montero · Accepted Answer · 2018-09-14 14:04:13Z

1

This will remove all the ' from left and right. For example: ''sample'' will be sample

Edited: fix in case PartNumber only contains '

UPDATE [T]
SET  [PartNumber] =
    CASE
        WHEN [X].[Left] IS NULL OR [X].[Right] IS NULL
            THEN ''
        ELSE
            SUBSTRING
            (
                 LEFT([T].[PartNumber], LEN([T].[PartNumber]) - ISNULL([X].[Right] - 1, 0)) -- Remove all the ' from the right
                ,ISNULL([X].[Left], 1) -- remove all the ' from the left
                ,LEN([T].[PartNumber])
            )
    END
FROM #temp AS [T]
CROSS APPLY
(
    SELECT
         [Left] = NULLIF(PATINDEX('%[^'']%', [T].[PartNumber]), 0) -- get the first position of a character distinct of ' (from the left)
        ,[Right] = NULLIF(PATINDEX('%[^'']%', REVERSE([T].[PartNumber])), 0) -- get the last position of a character distinct of ' (from the right)
) AS [X];

edited Sep 14, 2018 at 14:04

answered Sep 13, 2018 at 21:49

Hector Montero

2211 silver badge5 bronze badges

1 Comment

Hector Montero Over a year ago

This will fail if PartNumber only contains apostrophe.

HABO · Accepted Answer · 2018-09-14 00:43:40Z

Another approach uses Left() and Right() to sniff the first and last characters, then extracts an appropriate substring

NB: This fails if PartNumber consists of a single apostrophe, i.e. the first and last character is an apostrophe.

-- Sample data:
declare @Samples as Table ( PartNumber VarChar(16) );
insert into @Samples ( PartNumber ) values
  ( 'BC.QT.000002' ), ( '''CP.AC.''000010''' ),
  ( '''CP.AL.000013' ), ( '''CP.A''L.000016''' ),
  ( '''CP.AL.000024''' );

-- Play with it:
select PartNumber,
  Substring( PartNumber,
    case when Left( PartNumber, 1 ) = '''' then 2 else 1 end,
    Len( PartNumber ) - case
      when Left( PartNumber, 1 ) = '''' and Right( PartNumber, 1 ) = '''' then 2
      when Left( PartNumber, 1 ) = '''' or Right( PartNumber, 1 ) = '''' then 1
      else 0 end ) as TrimmedPartNumber
  from @Samples;

Tip: From Len(): "Returns the number of characters of the specified string expression, excluding trailing blanks." DataLength does not exclude trailing blanks. For Unicode strings you can use DataLength( UnicodeStringExpression ) / DataLength( N'#' ) to get the length in characters. In general DataLength( Left( Coalesce( StringExpression, '#' ), 1 ) ) will return the number of bytes per character.

Collectives™ on Stack Overflow

How to escape a ' character in the SQL update with replace function?

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related