CODEWITHSUNDEEP
sqlsql-serversql-server-2008
VasyPupkin
VasyPupkin

Reputation: 153

How to select an entry from the rows of numbers

I need to select number like 36/5 from string, but does not get the result. I can only select number 36.

Code:

DECLARE @s nvarchar(255);
SET @s = 'http://kbyte.ru/ru/Programming/Sources.aspx?id=36/5&mode=show'
-- берем переднюю  часть строки, начиная с числа
SELECT @s = SUBSTRING(@s, PATINDEX('%[0-9]%', @s), LEN(@s));
-- отсекаем хвост до числа
SELECT @s = SUBSTRING(@s, 0, PATINDEX('%[^0-9]%', @s));
-- выводим
SELECT @s;

Upvotes: 1

Views: 74

Answers (2)

BJones
BJones

Reputation: 2460

An alternative if you're always going to retrieve 4 characters. Otherwise you can tweak it to how you need.

 DECLARE @s nvarchar(255);
 SET @s = 'http://kbyte.ru/ru/Programming/Sources.aspx?id=36/5&mode=show'
 SELECT @s= SUBSTRING(@s,(CHARINDEX('id=',@s)+3),4)
 SELECT @s;

EDIT

This may solve the problem of limited characters... This will work if your value is always between "id=" and "&mode"

 DECLARE @s nvarchar(255);
 DECLARE @int INT
 SET @s = 'http://kbyte.ru/ru/Programming/Sources.aspx?id=36/5&mode=show'
 SELECT @int = CHARINDEX('&mode',@s) - (CHARINDEX('id=',@s)+3)
 SELECT @s= SUBSTRING(@s,(CHARINDEX('id=',@s)+3),@int)
 SELECT @s;

Upvotes: 1

Atoadaso
Atoadaso

Reputation: 133

If your string is always going to be in that format just add / to your second pattern index.

DECLARE @s nvarchar(255);
SET @s = 'http://kbyte.ru/ru/Programming/Sources.aspx?id=36/5&mode=show'
SELECT @s = SUBSTRING(@s, PATINDEX('%[0-9]%', @s), LEN(@s));
SELECT @s = SUBSTRING(@s, 0, PATINDEX('%[^0-9/]%', @s));
SELECT @s;

Upvotes: 2

Related Questions