CODEWITHSUNDEEP
sql-serversubstring
Ehsan Akbar
Ehsan Akbar

Reputation: 7301

Get the right number of a value in SQL Server using substring

I have this data in my SQL Server:

1/2
1/4
2/23
12/13
1/10
...

I need to change these to 002,004,023,013,010,..

I just need to select the end (RIGHT) part number of my value. I got the LEFT part using this code before:

RIGHT('000' + LEFT(SheetNumber, CHARINDEX('/', SheetNumber) - 1), 3)

Upvotes: 2

Views: 84

Answers (3)

GuidoG
GuidoG

Reputation: 12014

First you need to get the right part like this :

declare @table table (SheetNumber varchar(10))
insert into @table values ('1/2')
insert into @table values ('2/23')

select Right(SheetNumber, len(SheetNumber) - CHARINDEX('/',SheetNumber)) from @table

This will give you this :

2
23

Now build on this to pad 0 in front of it 

declare @table table (SheetNumber varchar(10))
insert into @table values ('1/2')
insert into @table values ('2/23')

select Right('000' + Right(SheetNumber, len(SheetNumber) - CHARINDEX('/',SheetNumber)), 3) from @table

and that will give you this : 

002
023

Upvotes: 3

John Cappelletti
John Cappelletti

Reputation: 81960

Your were almost there

Declare @YourTable table (SheetNumber varchar(50))
Insert Into @YourTable values
('1/2'),
('1/4'),
('2/23'),
('12/13'),
('1/10')

Select right('000'+substring(SheetNumber,CHARINDEX('/',SheetNumber) + 1,10),3)
 From @YourTable

Returns

(No column name)
002
004
023
013
010

Upvotes: 3

Jatin Patel
Jatin Patel

Reputation: 2104

Try this,

SELECT Right('000' + RIGHT(SheetNumber,LEN(SheetNumber) - CHARINDEX('/',SheetNumber) ), 3)

OR

SELECT RIGHT(REPLACE(@SheetNumber,'/','/000'),3)

Upvotes: 6

Related Questions