April 11, 2022 at 12:00 am
Comments posted to this topic are about the item Removing Text From All Rows
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 11, 2022 at 8:39 am
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
April 11, 2022 at 11:46 am
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
April 11, 2022 at 1:42 pm
Agreed, none of the answers is correct.
April 11, 2022 at 7:20 pm
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
April 12, 2022 at 12:19 am
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;
April 12, 2022 at 12:41 am
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
April 13, 2022 at 3:11 pm
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....
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
April 15, 2022 at 1:32 pm
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
April 15, 2022 at 4:45 pm
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
April 19, 2022 at 1:42 am
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;
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply