Need a Function

Posted by Dora743 under Sql Server on 12/9/2009 | Views : 1099 | Status : [Member] | Replies : 3
Hello one and all,
I need a function for the following one .. I am having my input as some Ap1z-123. I would like to separate 123 from string and i would like to print that . Like that i can have many but i should get only the numeric part appended to the end of the string.

Few examples :
Input Output
---------------------------------------------------------
AP12Z/111 111
-------------------------------------------------------------
ap23e_234 234
---------------------------------------------------------
ABN4E-456 456


Like that i can have several inputs but the output should be only numbers

Dorababu


Responses

Posted by: Pandians on: 12/9/2009 [Member] [MVP] Silver

Up
0
Down
Hi

Try this....

Creating Function :
CREATE FUNCTION DBO.Split (@Input VARCHAR(MAX)) RETURNS BIGINT AS
BEGIN
RETURN RIGHT(@Input,PATINDEX('%[^0-9]%',REVERSE(@Input))-1)
END

Using the Function :
SELECT Input,DBO.Split(Input) 'Output' FROM <TableName>

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Dora743, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dora743 on: 12/9/2009 [Member] Starter

Up
0
Down

Hi ,

Thanks for your reply. It works fine[:)]. Another help
If i would like to display the left part i.e if i am having qqq3e_123 as input i would like to display qqq3e

Dorababu

Dora743, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dora743 on: 12/9/2009 [Member] Starter

Up
0
Down
Hey got the solution for it. Function goes as follows

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)
SELECT @n = left(@str,len(@str) - @m)
print @j
print @n
set @Result=@j+1
set @Result=@n+@Result
print @Result

Dorababu

Dora743, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response