asked
trimming value from a string
Hello Techies, Please help me on this critical situation. i want to get the numeric value appearing after after 'USE' 'YMM OF apple use 2030831448' 'YMM OF Banana use 27678109' I used RIGHT(RTRIM(FixMessage),8)) but it works only with 2nd example. the length of numeric value is not same. Thank You all for your valuable suggestion.
Good morning, Use a CHARINDEX to find the location of 'use' then +4 to its location to account for the 'use ' then use SUBSTRING to take the the chars between the end of 'use ' and n. DECLARE @Example TABLE ( String VARCHAR(50) ); INSERT INTO @Example ( String ) VALUES ( 'YMM OF apple use 2030831448' ) , ( 'YMM OF Banana use 27678109' ) SELECT String , SUBSTRING(String, CHARINDEX('use', String) + 4, 50) AS 'Your Number' FROM @Example
works well unless there is any risk that 'use' is found in the string more than once. I'd REVERSE the original and then look for the 'first' space ... DECLARE @var VARCHAR(100) SET @var = 'YMM OF Banana use 27678109' SELECT right(@var,PATINDEX('% %',REVERSE(@var)))
15 People are following this question.
