How to delete part of string

Transact-SQL
1

hi all,
want to delete part of string based on @stringid

declare @stringid varchar(100) = '22'

declare @temp table
(
string varchar(100)
)

insert into @temp
select 'xyz:abc:123:21;xyz:bca:321:22;xya:abb:123:25'

select * from @temp

seeking output as below how can we do this ?

xyz:abc:123:21;xya:abb:123:25

2

Is there always only one value that matches and needs deleted, or could there be multiple substrings that need deleted? That is, is something like this possible?:

select 'xyz:abc:123:21; xyz:bca:321:22 ;xya:abb:123:25; xyz:def:654:22'

3

Hi Scott

Thanks for replying,

Yes, there will be chances of multiple substrings to be deleted based on id

4

where or from what are you getting these values from? they look like pseudo json format.

SQL 2016 string_split & @JeffModen DelimitedSplit8K

select replace(string ,value+ ';','') as goat, *
  from (
			select *
			   from @temp
			   cross apply string_split(string, ';') 
		) a
	cross apply DelimitedSplit8K(a.value,':') d
	where d.Item = @stringid
5 
declare @stringid varchar(100) = '22'

declare @temp table
(
string# int identity(1, 1) not null,
string varchar(100)
)

insert into @temp values
    /*2nd string needs removed*/
    ('xyz:abc:123:21;xyz:bca:321:22;xya:abb:123:25'),
    /*no string needs removed*/
    ('xyz:abc:123:21;xyz:bca:321:23;xya:abb:123:25'),
    /*note: 1st, 3rd and 5th strings need removed*/
    ('xyz:aaa:111:22;xyz:abc:123:21;xyz:bca:321:22;xya:abb:123:25;xyz:def:654:22') 

UPDATE t
SET string = new_string
FROM @temp t
CROSS APPLY (
    SELECT STUFF((
        SELECT ';' + Item
        FROM dbo.DelimitedSplit8K(string, ';') AS ds
        WHERE RIGHT(Item, LEN(@stringid) + 1) <> ':' + @stringid
        ORDER BY ItemNumber
        FOR XML PATH(''), TYPE).value('.', 'varchar(8000)')
        ,1, 1, '') AS new_string
) AS ca1
WHERE CHARINDEX(':' + @stringid + ';', string) > 0 

SELECT * FROM @temp
1 Like
6

If you're using SQL Server 2017 or above, this can be simplified a bit by changing the concatenation of the XML PATH stuff to STRING_AGG(). Are you using 2017 or above?

7

I don't assume a really recent version of SQL Server, since most people seem not to have one. Where I'm DBA, I only have one instance left at 2012 (SSRS), the rest are all 2016/2017, but that's not that common, probably mostly due to licensing costs.

8

Yep... I get that. Absolutely no slight intended in my question to the OP.

I'm kind of in the same boat as most people and don't have anything at work greater than 2017 and so I definitely get it. We'll have a need for folks to post the XML PATH method of concatenation for the next 4 to 8 years and totally appreciate why you posted that as a solution. I'd have done the same thing.

It's kind of like the days when 2005 and ROW_NUMBER() first came out. Shoot, I still post some 2005/2008 solutions because, similar to what you said, some people don't even have 2012 in place yet.

Bottom line is, I'm right there with you and, unless one has some prior knowledge of otherwise (and the OP on this thread provided none), it's almost always better to post something that will work in the most versions, like you did. You'll never need to justify that to me.

I was just asking the OP if they had a later version... just like I did back when 2005 first came out.

1 Like
9

The previous repliers are all very knowledgeable (far more than I am) and their answers look good for what you specifically asked. However, I can't help noticing that the data in your strings looks like it should be a 4-column table, which might make the underlying problem much easier to solve, using standard SQL manipulations instead of string manipulations, which are much messier and harder to maintain. Regards.

10

for me, it is best to avoid string manipulation if at all possible

  1. If the data is stored like he provided, that is worrisome
  2. if the data is coming to him from an api, that is worrisome

I am super curious to know what is the source of the data.

to me it really looks like JSON

"goobs": {
        "xyz": 123,
        "abc": 21},
         {
           "xyz":321,
            "bca": 22
         }
1 Like
11

@ScottPletcher your query is working as expected thanks a lot.

@JeffModen am using SSMS 2017

@yosiasz for me data is coming from API.

Thank you all for you time and support.

12

@vajeed

SSMS isn't the deciding factor here. Which version of SQL Server are you using?

13

@JeffModen Version is 18.3.1

14

What type of api is it? .net, python, js? That is a most unusual data being returned for an api

15

No... that just the version number of SSMS. That's not the version of SQL Server itself.

Run the following using the :text results" output mode and post the results here.

SELECT @@VERSION;