Removing Text From All Rows

Question

Removing Text From All Rows

Steve Jones - SSC Editor

SSC Guru

Points: 736479
More actions
April 11, 2022 at 12:00 am

#4007884

Comments posted to this topic are about the item Removing Text From All Rows

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Carlo Romagnano SSC-Insane Points: 22682 More actions · Answer 1

WITH ctePC
AS ( SELECT expression
     FROM
       ( VALUES
           ('\1\cpu')
         , ('\1\ram')
         , ('\1\ssd')
         , ('\133\gpu')
         , ('\1\cooler')
         , ('\1\fan')) a (expression) )
select right(expression,charindex('\',reverse(expression))-1)
from ctePC

William Vach SSCertifiable Points: 5551 More actions · Answer 2

Good question but correct answer is None of the above. Answer should be:

WITH ctePC

AS ( SELECT expression

FROM

( VALUES

('\1\cpu')

, ('\1\ram')

, ('\1\ssd')

, ('\1\gpu')

, ('\1\cooler')

, ('\1\fan')) a (expression) )

select replace(expression, '\1\', '') from ctePC

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 3

Scott Arendt

SSCrazy Eights

Points: 8218

April 11, 2022 at 1:42 pm

#4013960

Agreed, none of the answers is correct.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Carlo Romagnano wrote:

WITH ctePC
AS ( SELECT expression
     FROM
       ( VALUES
           ('\1\cpu')
         , ('\1\ram')
         , ('\1\ssd')
         , ('\133\gpu')
         , ('\1\cooler')
         , ('\1\fan')) a (expression) )
select right(expression,charindex('\',reverse(expression))-1)
from ctePC

That's more "Bullet Proof", as well.

As a bit of a sidebar, it also demonstrates that the code beautifier on this site could use some work... the backslash in the '\' escaped the right hand single quote instead of observing it as a single quote to end the string.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

George Vobr SSChampion Points: 10009 More actions · Answer 5

In SQL Server 2017 (14.x) and later, it is possible to use the TRIM (Transact-SQL) function:

;WITH ctePC
AS ( SELECT expression
     FROM
       ( VALUES
           ('\1\cpu')
         , ('\1\ram')
         , ('\1\ssd')
         , ('\1\gpu')
         , ('\1\cooler')
         , ('\1\fan')) a (expression) )
SELECT TRIM ( '\1\' FROM expression ) FROM ctePC;

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

George Vobr wrote:

In SQL Server 2017 (14.x) and later, it is possible to use the TRIM (Transact-SQL) function:

;WITH ctePC
AS ( SELECT expression
     FROM
       ( VALUES
           ('\1\cpu')
         , ('\1\ram')
         , ('\1\ssd')
         , ('\1\gpu')
         , ('\1\cooler')
         , ('\1\fan')) a (expression) )
SELECT TRIM ( '\1\' FROM expression ) FROM ctePC;

In which case, you should include all 10 digits and the pray out loud that no one ever creates a part description that begins or ends with a digit.

The only way to make this "Bullet Proof" is to do like Carlo Romagnano did above or use a splitter that also returns the ordinal position of the elements.

That also mean to me that even if the intended correct answer were repaired, there still wouldn't be a correct answer. 😀 But that's also way beyond the scope of the intention of the question.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 7

If removing a consistent number of characters from the beginning of a string, I believe it would be best to use SUBSTRING as it will simply return characters based on their position in the string (a simple operation) as opposed to REPLACE which does a string comparison which is a more complex and expensive operation (especially when not using a binary collation, but even then it still needs to repetitively compare the searched-for string to the searched-in string, starting at every position of the searched-in string (minus the length of the searched-for string)). Hence, a simple SUBSTRING(ctePC.expression, 4, 1000) works and should be more efficient. Just be sure to specify a "length" (3rd parameter to SUBSTRING) that is at least the max size of the column (which is fine since SUBSTRING does not error when the length requested is beyond the length of the input value).

Alternatively, as others here have pointed out, it would be more robust to allow for different and/or additional digits. This can be done more simply and efficiently than using REVERSE by using CHARINDEX alone and making use of the optional 3rd parameter to start searching for \ after the first character: CHARINDEX('\', ctePC.expression, 3) + 1 . The + 1 is to make sure that the first character returned isn't the 2nd \ . If it's not obvious, this replaces the hard-coded starting position (i.e. 2nd parameter) of SUBSTRING.

As Jeff pointed out, using TRIM is not an option as the input value is an array of individual characters, not a string of multiple characters. Hence, specifying \1\ in TRIM will remove all occurrences of both \ and 1 from both the beginning and ending of the passed-in string (the second \ in \1\ is ignored as a duplicate of the first \ ). In the example below, I included a value that ends with 1 to show why TRIM does not work here.

;WITH ctePC
AS ( SELECT expression
 FROM ( VALUES
   ('\1\cpu')
 , ('\12\ram')
 , ('\123\ssd')
 , ('\1\gpu')
 , ('\1\cooler')
 , ('\1\cdrom1')
 , ('\1\fan')) a (expression) )
 SELECT ctePC.expression AS [Original],
           TRIM('\1\' FROM ctePC.expression)    AS [TRIM], -- ' <-- partial fix for
           REPLACE(ctePC.expression, '\1\', '') AS [REPLACE], -- '  syntax highlighter
           SUBSTRING(ctePC.expression, 4, 1000) AS [SUBSTRING],
           SUBSTRING(ctePC.expression, CHARINDEX('\', ctePC.expression, 3) + 1, 1000) -- '
                                                AS [SUBSTRING+CHARINDEX]
 FROM ctePC;

returns:

Original        TRIM      REPLACE     SUBSTRING    SUBSTRING+CHARINDEX
\1\cpu          cpu       cpu         cpu          cpu
\12\ram         2\ram     \12\ram     \ram         ram
\123\ssd        23\ssd    \123\ssd    3\ssd        ssd
\1\gpu          gpu       gpu         gpu          gpu
\1\cooler       cooler    cooler      cooler       cooler
\1\cdrom1       cdrom     cdrom1      cdrom1       cdrom1
\1\fan          fan       fan         fan          fan

Take care,

Solomon....

This reply was modified 2 years, 8 months ago by Solomon Rutzky.
This reply was modified 2 years, 8 months ago by Solomon Rutzky.

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Carlo Romagnano SSC-Insane Points: 22682 More actions · Answer 8

If you assume that the string always starts with '\1\' (as the original post) and you do not know then length of text then you can use STUFF instead of SUBSTRING.

WITH ctePC
AS ( SELECT expression
FROM
( VALUES
('\1\cpu')
, ('\1\ram')
, ('\1\ssd')
, ('\1\gpu')
, ('\1\cooler')
, ('\1\fan')) a (expression)
)
SELECT STUFF(ctePC.expression,1,3,'') FROM ctePC

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Like a lot of the other answers on this thread, that's a lot of non-bullet-proof IF's that will eventually result in silent errors producing an incorrect answer.

Don't assume. Yeah... you might be able to get things done super quick but that means that someday you'll also need to squirt out a fix "super quick". Do it right the first time. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

George Vobr SSChampion Points: 10009 More actions · Answer 10

I had some time, so "I had fun" and put together another way to find the part name from the CTE.

The query below displays the text of the part name correctly if it is preceded by a backslash considered to be the end of the text of the hierarchy value of this part:

;WITH ctePC
AS ( SELECT expression
     FROM
       ( VALUES
           ('\1\CPU Intel Core i9-12900K')
         , ('\2.1a\RAM DDR4\16GB')
         , ('\2.1b\SSD Samsung 870 QVO 8TB')
         , ('\3\GPU Radeon HD 7950/7970')
         , ('\4.0\cooler')
 , ('\4.1\fan')
 , ('\5\3D Printer')
         , ('\etc.')) a (expression) )
        
SELECT SUBSTRING (ctePC.expression
, (CHARINDEX ('\', SUBSTRING (ctePC.expression, 2, (LEN(ctePC.expression))))+2)
, (LEN(ctePC.expression)))  AS PartName
       FROM ctePC; 


PartName
------------------------
CPU Intel Core i9-12900K
RAM DDR4\16GB
SSD Samsung 870 QVO 8TB
GPU Radeon HD 7950/7970
cooler
fan
3D Printer
etc.
------------------------
8 rows

Another way to find the position where the part name starts is to use the RIGHT () function in the CHARINDEX () function instead of the SUBSTRING () function:

SELECT SUBSTRING (ctePC.expression 
       , (CHARINDEX ('\', RIGHT (ctePC.expression, (LEN(ctePC.expression)-1)))+2)
, (LEN(ctePC.expression))) AS PartName
       FROM ctePC;