CODEWITHSUNDEEP
sql-serversubstring
Ehsan Akbar
Ehsan Akbar

Reputation: 7301

Substring and split in sql server

I have these data in my sql server as you can see here :

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

I need to change these to 001,001,002,012,001,..

I use this .but it doesn't work 

LEFT(SheetNumber,LEN(SheetNumber)-CHARINDEX('/',SheetNumber))

My query

SELECT  [Id]

      ,LEFT(SheetNumber,LEN(SheetNumber)-CHARINDEX('/',SheetNumber))


      ,[SubmitDateTime]
  FROM [SPMS2].[dbo].[Lines] where SheetNumber  like '%/%'

enter image description here

Upvotes: 2

Views: 101

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Try this

DECLARE @tbl TABLE(SheetNumber VARCHAR(100));
INSERT INTO @tbl VALUES
 ('1/2')
,('1/4')
,('2/23')
,('12/13')
,('1/10');

SELECT STUFF(
   (
    SELECT ',' + REPLACE(STR(LEFT(SheetNumber,CHARINDEX('/',SheetNumber) - 1),3),' ','0')
    FROM @tbl
    FOR XML PATH('')
   ),1,1,'');

The result

001,001,002,012,001

Upvotes: 1

Mahedi Sabuj
Mahedi Sabuj

Reputation: 2944

Try this

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

Upvotes: 2

Mark
Mark

Reputation: 2063

You dont need to use LEN. Just use 

LEFT(SheetNumber,CHARINDEX('/',SheetNumber) - 1)

To make it into 3 digits with 0 in the front, you could use something like this

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

Upvotes: 3

Related Questions