CODEWITHSUNDEEP
sqlsql-server
Deqing
Deqing

Reputation: 14642

Increasing a number in a string

There are some objects encoded as key:value strings and stored in a table, I'd like to increase sequence number of all objects, which is one field in the object.

For example:

ID   Value
--------------------------
504  s:0;d:n;e:test;
506  s:1;d:y;e:branch;
507  s:2;d:y;e:;

I'd like to change them to:

ID   Value
--------------------------
504  s:1;d:n;e:test;
506  s:2;d:y;e:branch;
507  s:3;d:y;e:;

Is there a simple way to do this?

Upvotes: 0

Views: 322

Answers (5)

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

Is there a simple way to do this?

No not really.

You can find the positions of s: and d: and then use that to extract the number inbetween, increase it by one and stuff it back into where it belongs.

declare @T table
(
  ID int,
  Value varchar(50)
);

insert into @T values
(504, 's:0;d:n;e:test;'),
(506, 's:1;d:y;e:branch;'),
(507, 's:2;d:y;e:;');

select T.ID,
       stuff(T.Value, P.S, P.D - P.S - 1, S.Value) as NewValue
from @T as T
  cross apply (values(charindex('s:', T.Value) + 2, 
                      charindex('d:', T.Value))) as P(S, D)
  cross apply (values(substring(T.Value, P.S, P.D - P.S - 1) + 1)) as S(Value)

A version where you find the ; after s: instead of d: as suggested by Eric in a comment.

select T.ID,
       stuff(T.Value, S.Pos, SEnd.Pos - S.Pos, V.NewValue) as NewValue
from @T as T
  cross apply (values(charindex('s:', T.Value) + 2)) as S(Pos)
  cross apply (values(charindex(';', T.Value, S.Pos))) as SEnd(Pos)
  cross apply (values(substring(T.Value, S.Pos, SEnd.Pos - S.Pos) + 1)) as V(NewValue)

Upvotes: 6

neer
neer

Reputation: 4082

You can as the below:

DECLARE @val VARCHAR(100) = 's:12;d:n;e:test;'
SELECT  REPLACE(@val, ':' + SUBSTRING(@val, 3,  PATINDEX('%;d:%',  @val) - 3) + ';',  ':' + CAST(SUBSTRING(@val, 3,  PATINDEX('%;d:%',  @val) - 3)+ 1 AS VARCHAR(MAX)) + ';')

Result: s:13;d:n;e:test;

Upvotes: 0

Eric J. Price
Eric J. Price

Reputation: 2785

This accounts for empty or non-integer values; it will ignore them in the event they can't be incremented by one.

-- Build Test Data
IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test
CREATE TABLE #test (ID INT, Value VARCHAR(100))

INSERT #test
VALUES 
(504,'s:0;d:n;e:test;'),
(506,'s:1;d:y;e:branch;'),
(507,'s:2;d:y;e:;'),
(508,'s:;d:y;e:;'),
(509,'s:xyz;d:y;e:;');

-- Update S: values
WITH sVals AS
(
    SELECT ID, Value, TRY_PARSE(SUBSTRING(Value,CHARINDEX('s:',Value)+2,CHARINDEX(';',Value,CHARINDEX('s:',Value))-(CHARINDEX('s:',Value)+2)) AS INT) AS sVal
    FROM #test AS t
)
UPDATE s
SET Value = IIF(sVal IS NOT NULL, STUFF(Value,CHARINDEX('s:',Value)+2,CHARINDEX(';',Value,CHARINDEX('s:',Value))-(CHARINDEX('s:',Value)+2),sVal+1), Value)
FROM sVals AS s

-- Check the results
SELECT *
FROM #test

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28900

Using the split string functions from here:Split strings the right way – or the next best way

 declare @string varchar(max)
set @string='504  s:0;d:n;e:test;'

;with cte as(select * from 
[dbo].[SplitStrings_Numbers]
 (@string,':'))
select b.item+1 from cte c
cross apply
(select * from [dbo].[SplitStrings_Numbers](c.item,';')) b
where isnumeric(b.item)=1

Upvotes: 0

K Scandrett
K Scandrett

Reputation: 16540

 DECLARE @val nvarchar(200) 

 SET @val = 's:1;d:y;e:branch;'

 SELECT 's:' + CONVERT(nvarchar(100), CONVERT(INT, SUBSTRING(@val, charindex(':', @val) + 1, charindex(';', @val) - charindex(':', @val) -1)) + 1) + SUBSTRING(@val, charindex(':', @val),1000)

You can use what's in the SELECT's query in an UPDATE statement to change the table values

Upvotes: 0

Related Questions