update stored procedure to add one to the maximum value of nvarchar column

Hi: I need update stored procedure to add one to the maximum value of nvarchar column.
My table name is Sections.
My column names are SectionNumber,  HierarchicalNumber
The data stored this way:
SectionNumber,  HierarchicalNumber
        1                      001/005/007
        2                      001/005/007/001
        3                      001/005/007/002
        4                     001/005/007/003
        5                     Blank
In my case , I already have SectionNumber 5 and also, I have the father HierarchicalNumber which is in this case = 001/005/007.
I need to have 001/005/007/004 in the Blank HierarchicalNumber of  SectionNumber 5.
Here is another example:
SectionNumber,  HierarchicalNumber
        70150                      109/905/048
        70151                      109/905/048/760
        70152                      109/905/048/761
        70153                     109/905/048/762
        70154                     Blank
In my case , I already have SectionNumber  70154 and also, I have the father HierarchicalNumber which is in this case = 109/905/048.
I need to have 109/905/048/763 in the Blank HierarchicalNumber of  SectionNumber  70154.

Anyone can help me please!
Thanks in advance
Mohammad Alsolaimanapplication programmerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Where are you getting the HierarchicalNumber from?
The best approach would be for you to use the HeirarchyId data type in SQL Server. However, leading zeros would then not be allowed. You could make that a UI formatting issue though. That said, here is a working SQL based approached. I modified a copy of udfSplit that I found somewhere (don't remember where, and just made it return a few extra fields etc). udfSplitEx is slooow! I wouldn't use it on a laaaaaarge table. But I think for your purposes here, it will work nicely.
declare @next varchar(100) = '109/905/048' --also try parts e.g. 001, 001/005, 001/005/007

;with Data(SectionNumber, HierarchicalNumber) as (
	select 1, '001/005/007'
	union select 2, '001/005/007/001'
	union select 3, '001/005/007/002'
	union select 4, '001/005/007/003'
	union select 5, '109/905/048/762 '
select @next = @next + '/' + right('00' + cast(max(cast(Value as int)) + 1 as varchar), 3)
from Data
	outer apply dbo.udfSplitEx(HierarchicalNumber,'/') Data2
where SplitPath like @next + '/%' and SplitPath not like @next + '/%/%'

select @next

Open in new window

and the udfSplitEx function
CREATE function [dbo].[udfSplitEx](
 @String nvarchar (max),
 @Delimiter nvarchar (10)
--returns @ValueTable table ([Value] nvarchar(max), [SplitPos] int null, [SplitPath] nvarchar(max) null)
returns @ValueTable table ([Value] nvarchar(max), [SplitPos] int null, [SplitIndex] int null, [SplitPath] nvarchar(max) null)
 declare @PathString nvarchar(max), @OriginalString nvarchar(max)
 declare @NextString nvarchar(max)
 declare @Pos int, @SplitPos int, @RunningPos int
 declare @NextPos int
 declare @CommaCheck nvarchar(1)
 set @NextString = ''
 set @OriginalString = @String
 set @CommaCheck = right(@String,1) 
 --Check for trailing Comma, if not exists, INSERT
 --if (@CommaCheck <> @Delimiter )
 set @String = @String + @Delimiter
 --Get position of first Comma
 set @Pos = charindex(@Delimiter,@String)
 set @RunningPos = @Pos
 set @NextPos = 1
 set @SplitPos = 1
 --Loop while there is still a comma in the String of levels
 while (@pos <>  0)  

  set @NextString = substring(@String, 1, @Pos - 1)
  set @PathString = substring(@OriginalString, 1, @RunningPos-1)
  --insert into @ValueTable ([Value], [SplitPos], SplitPath) Values (@NextString, @SplitPos, @PathString)
  insert into @ValueTable ([Value], [SplitPos], SplitIndex, SplitPath) Values (@NextString, @SplitPos, @RunningPos, @PathString)
  set @String = substring(@String, @pos + LEN(@Delimiter), len(@String))
  set @NextPos = @Pos + LEN(@Delimiter)
  set @pos  = charindex(@Delimiter,@String)
  set @RunningPos = @RunningPos + @Pos --+ len(@Delimiter) - 1
  set @SplitPos = @SplitPos + 1

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mohammad Alsolaimanapplication programmerAuthor Commented:
thanks 2 all of u
Mohammad Alsolaimanapplication programmerAuthor Commented:
oops wrong points
it should be MlandaT solution.
any one help me correct my fault, please!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.