this is my string:
lid://abcde.ln.ln_dev_510
how to get the number after '_' using LTRIM, RTRIM.
Thank You.
2
-
What have YOU tried so far? Please show us some effort of your own!marc_s– marc_s2016-06-22 12:08:54 +00:00Commented Jun 22, 2016 at 12:08
-
Questions seeking help must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers. See: How to create a Minimal, Complete, and Verifiable example.Gustavo Morales– Gustavo Morales2016-06-22 12:44:36 +00:00Commented Jun 22, 2016 at 12:44
Add a comment
|
3 Answers
LTRIM and RTRIM are for removing trailing and leading spaces in string. You can simply get the number using the following query:
DECLARE @t VARCHAR(MAX)='lid://abcde.ln.ln_dev_510'
SET @t = REVERSE(@t)
SELECT REVERSE(SUBSTRING(@t,1,CHARINDEX('_',@t)-1))
answered Jun 22, 2016 at 12:28
Vahid Farahmandian
6,6227 gold badges48 silver badges68 bronze badges
Comments
In you case using PARSENAME you can get the values after the last _.
DECLARE @StringValue AS VARCHAR (500) = 'lid://abcde.ln.ln_dev_510';
SELECT PARSENAME(REPLACE(PARSENAME(@StringValue, 1), '_', '.'), 1)
Output will be:
510
Comments
At first we REVERSE the string backward to find last occurrence of '_' symbol (since SQL Server got no other way to search for last occurrence of string in another, we need to reverse the string). The number we get minus 1 is exactly number of symbols we must to take from RIGHT part of original string.
DECLARE @str nvarchar(max) = 'lid://abcde.ln.ln_dev_510'
SELECT RIGHT(@str,CHARINDEX('_',REVERSE(@str))-1)
Output:
510
2 Comments
Mike Cluck
Perhaps you could edit your question to explain the functions you used since OP does not seem to be familiar with their usage.
gofr1
Explanation added! Thanks for noticing! :)