This should work :
SELECT SUBSTRING(id,CHARINDEX('-',id)+1,LEN(id)) from test2
greater than values after Hypen
Shambhu Rai
1,406
Reputation points
Hi Expert,
create table test2
(id string)
insert into test2
values('10-25'),
('0-5')
I wanted take last values from the input
i.e
output
id
25
5
4 answers
Sort by: Most helpful
-
Nandan Hegde 29,911 Reputation points MVP
2022-08-18T13:13:02.833+00:00 -
Yitzhak Khabinsky 25,206 Reputation points
2022-08-18T13:13:12.513+00:00 Hi @Shambhu Rai ,
Please try the following solution.
SQL
-- DDL and sample data population, start DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(1000)); INSERT INTO @tbl (tokens) VALUES ('10-25'), ('0-5'); -- DDL and sample data population, end DECLARE @separator CHAR(1) = '-'; SELECT t.* , c.value('(/root/r[last()]/text())[1]', 'INT') AS Result FROM @tbl AS t CROSS APPLY (SELECT TRY_CAST('<root><r>' + REPLACE(tokens, @separator, '</r><r>') + '</r></root>' AS XML)) AS t1(c);
Output
+----+--------+--------+ | ID | tokens | Result | +----+--------+--------+ | 1 | 10-25 | 25 | | 2 | 0-5 | 5 | +----+--------+--------+
-
VinodKumar-0434 276 Reputation points
2022-08-18T13:14:44.937+00:00 Hi @Shambhu Rai , Thanks for reaching out to Microsoft Q&A
You can use below statement
select RIGHT(id, CHARINDEX('-', REVERSE(id)) -1) from test2
If you find the answer useful, please accept the answer and upvote. Thanks
-
Isabellaz-1451 3,616 Reputation points
2022-08-19T07:18:54.803+00:00 Hi @Shambhu Rai
please also check :select reverse(SUBSTRING(REVERSE('10-11-45-78'),0,CHARINDEX('-',REVERSE('10-11-45-78'))))
Best Regards,
Isabella