Need a function for the following one

Question

I am having my string like this AP1BC_77.. I would like to display only 77 like that if we have any sort of string but i would like to display only the numeric appended to the end of the characters. Can any one help me please..

Answer 1

0

SELECT SUBSTRING('AP1BC_77',CHARINDEX('_','AP1BC_77',1) + 1,1000)

Several assumptions here, mainly there will only be one underscore and that underscore always precedes the number portion of the string. Ping back with more info if this is not what you're looking for

answered Dec 09 '09 at 02:46 AM

Scot Hauder 4
54 ● 1

Wow, Scott, I didn't realise length could be longer than the rest of the string. You learn something every day!

Dec 09 '09 at 05:25 AM David Wimbush

yet another assumption: the length will not be longer than 1000 char :)

Dec 09 '09 at 05:54 AM Scot Hauder 4

why not use the string length - start position as the length i.e. SELECT SUBSTRING('AP1BC_77',CHARINDEX('_','AP1BC_77',1) + 1,LEN('AP1BC_77')-CHARINDEX('_','AP1BC_77')) - saves having to assume!

Dec 09 '09 at 06:17 AM Kev Riley ♦♦

I'm lazy, didn't have to type as much and my employers pay by the hour, so they wouldn't like it! :)

Dec 09 '09 at 07:05 AM Scot Hauder 4

(comments are locked)

Answer 2

Assuming:

you could have some non-numeric chars 'AP1BC_XX' - show nothing
the appended numbers are ALWAYS preceded by an underscore, else show nothing

.

declare @string varchar(max)
set @string = 'AP1BC_77'

select
    case when isnumeric(substring(@string, charindex('_', @string)+1, len(@string)-charindex('_', @string)))=1
    		then substring(@string, charindex('_', @string)+1, len(@string)-charindex('_', @string))
    	else
    		''
    	end

Note: have used ISNUMERIC just to illustrate the point, but of course this isn't actually the best thing to do. Need really to test for just numbers (if this is a requirement). See this question

Answer 3

0

SELECT CAST(0x44 AS varchar)+REVERSE(UPPER(SUBSTRING(@@VERSION,4,2)))+CHAR(DATEPART(YEAR,GETDATE())
-CASE WHEN DATEPART(YEAR,GETDATE())%2=0 THEN 1945 ELSE 1944 END)

answered Dec 11 '09 at 02:05 AM

Scot Hauder 4
54 ● 1

(comments are locked)

Answer 4

0

I got the solution by the following one

declare @m varchar(100) declare @j varchar(100) declare @n varchar(100) DECLARE @str nvarchar(255) declare @Result varchar(255)

SET @str = 'q4q/4565' set @m=PATINDEX('%[^0-9]%',REVERSE(@str))-1 select @j= RIGHT(@str,PATINDEX('%[^0-9]%',REVERSE(@str))-1) print @j

answered Dec 10 '09 at 03:56 AM

Dora
1 ● 1 ● 1 ● 1

(comments are locked)

Need a function for the following one

Follow this question

Related Questions