select string removing two sets of characters

Question

Post reply

select string removing two sets of characters

HeftSteady

Old Hand

Points: 345
More actions
May 16, 2024 at 7:14 pm
Go to Answer
#4421787
Hi ,
I need help replacing a certain strings from a query response.
My searches are pointing to a replace in the actual table but I am looking for sql that returns a different value than what is in the table.
I am selecting a varchar(25) field and want to return the characters without the starting prefixes of D00 or 00 from the field.
1. For example if an ID is D00241 it should return 241 .
or
2. For example if an ID is 00242 it should return 242.

Phil Parkin

SSC Guru

Points: 246987

May 17, 2024 at 10:07 am

Answer

#4421907

DROP TABLE IF EXISTS #SomeString;

CREATE TABLE #SomeString
(
    Str1 VARCHAR(25)
);

INSERT #SomeString
(
    Str1
)
VALUES
('D00241')
,('00242 ')
,('Chicken Tikka Masala');

SELECT ss.Str1
      ,CASE
           WHEN ss.Str1 LIKE 'D00%' THEN
               STUFF (ss.Str1, 1, 3, '')
           WHEN ss.Str1 LIKE '00%' THEN
               STUFF (ss.Str1, 1, 2, '')
           ELSE
               ss.Str1
       END
FROM #SomeString ss;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Viewing 7 posts - 1 through 6 (of 6 total)

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

ScottPletcher SSC Guru Points: 101123 More actions · Answer 1

SELECT SUBSTRING(ID, ID_start_byte_2, 50) AS ID_you_wanted, ID AS original_ID

FROM dbo.table_name tn

CROSS APPLY ( SELECT CASE WHEN LEFT(ID, 1) = 'D' THEN 2 ELSE 1 END AS ID_start_btye_1 ) AS ca1

CROSS APPLY (SELECT CASE WHEN SUBSTRING(ID, ID_start_byte_1, 2) = '00' THEN ID_start_byte_1 + 2 ELSE ID_start_byte _1 END AS ID_start_byte_2 ) AS ca2

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 2

The proposed solutions seem to be a bit more code than is needed:

SELECT *
     , ActualID = substring(ss.Str1, patindex('%[1-9]%', ss.Str1), 25)
  FROM #SomeString ss

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Phil Parkin SSC Guru Points: 246987 More actions · Answer 3

Jeffrey Williams wrote:

The proposed solutions seem to be a bit more code than is needed:
SELECT *
     , ActualID = substring(ss.Str1, patindex('%[1-9]%', ss.Str1), 25)
  FROM #SomeString ss

Perhaps.

Scott's solution and mine more accurately match the specific requirement. Yours left-truncates 'E00241', for example

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 4

Phil Parkin wrote:

Jeffrey Williams wrote:
The proposed solutions seem to be a bit more code than is needed:
SELECT *
     , ActualID = substring(ss.Str1, patindex('%[1-9]%', ss.Str1), 25)
  FROM #SomeString ss
Perhaps.
Scott's solution and mine more accurately match the specific requirement. Yours left-truncates 'E00241', for example

Okay - if we only want to affect those rows that specifically start with those prefixes:

SELECT *
     , ActualID = CASE WHEN ss.Str1 LIKE '00%' OR ss.Str1 LIKE 'D00%'
                       THEN substring(ss.Str1, patindex('%[1-9]%', ss.Str1), 25)
                       ELSE ss.Str1
                   END
  FROM #SomeString ss

Still simpler that multiple STUFF's depending on which prefix is found - or stacked CROSS APPLY to get the start position for a SUBSTRING.

My solution would handle any prefix - yours and Scott's would need to be modified if a different prefix needs to be included or the actual data can contain many different prefixes and the OP was just giving an example of one of those.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Phil Parkin SSC Guru Points: 246987 More actions · Answer 5

It really depends on the data.

Try injecting ''00X11' into your version. It removes the 'X', which is not one of the specified requirements. I'm all for elegant code, and maybe the data is such that your solution works well. But without knowing all the data, maybe not.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel