Need a Function

Posted by Dora743 under Sql Server on 12/9/2009 | Views : 2270 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

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

Latest Posts