question

AlexKlap avatar image
AlexKlap 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.
sql-server-2008sql-server-2005t-sqldynamic-sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
@SQLShark avatar image
@SQLShark answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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)))
2 Likes 2 ·
Thank You Gentlemen...... Merry Christmas to all...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.