I am working in SQL server. I have some number like 130-0029. I need to get the integer after the - delimiter out of it. So in this example I need to return 29. These are the following scenarios I have with this,
130-00000029 or 130-0029. 0, else get actual number like 29.Please advise/suggest me on this.
Here is an example:
declare @s varchar(100) = '130-0029'
select cast(substring(@s, patindex('%[-]%', @s) + 1, len(@s)) as int)
You may need to cast to some numeric if the number overflows ineteger type.
Try this:
DECLARE @num varchar(50) = '130-0029'
SELECT CAST(SUBSTRING(@num, CHARINDEX('-', @num) + 1, LEN(@num)) AS INT)
Here is a fiddle.
This also works if the - is missing. But if what follows is not a number it will give an error.
declare @string as varchar(100)
set @string = '130-0029'
select convert(int,Right(@string, LEN(@string)-CHARINDEX('-', @string)))
Probably not so different than other answers, but this works using STUFF too:
DECLARE @String VARCHAR(10) = '130-0029';
SELECT CONVERT(INT, STUFF(@String, 1, CHARINDEX('-', @String), ''));
See this running in Query Stack Exchange.
