x

Need a function for the following one

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..

more ▼

asked Dec 09, 2009 at 01:55 AM in Default

Dora gravatar image

Dora
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first
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

more ▼

answered Dec 09, 2009 at 02:46 AM

Scot Hauder 4 gravatar image

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, 2009 at 05:25 AM David Wimbush
yet another assumption: the length will not be longer than 1000 char :)
Dec 09, 2009 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, 2009 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, 2009 at 07:05 AM Scot Hauder 4
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Dec 09, 2009 at 06:28 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.2k 47 49 76

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Dec 10, 2009 at 03:56 AM

Dora gravatar image

Dora
1 1 1 1

(comments are locked)
10|1200 characters needed characters left
 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) 
more ▼

answered Dec 11, 2009 at 02:05 AM

Scot Hauder 4 gravatar image

Scot Hauder 4
54 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x58

asked: Dec 09, 2009 at 01:55 AM

Seen: 956 times

Last Updated: Dec 09, 2009 at 01:55 AM