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

avatar image

Dora
1 1 1 2

(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

avatar image

Scot Hauder 4
54 2 2

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

avatar image

Kev Riley ♦♦
64k 48 61 81

(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

avatar image

Dora
1 1 1 2

(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

avatar image

Scot Hauder 4
54 2 2

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

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:

x68

asked: Dec 09, 2009 at 01:55 AM

Seen: 1107 times

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

Copyright 2016 Redgate Software. Privacy Policy