Solved

Need help converting simple function in SQL Server from Oracle...

Posted on 2009-05-14
5
220 Views
Last Modified: 2012-05-07
I am an Oracle PL/SQL guy but have hit an issue in trying to get this function converted to SQL Server.

The TO_CHAR piece of this doesn't exist (among other things).

Can someone please help me convert this?

Thanks in advance!

B
CREATE OR REPLACE FUNCTION format_sequence (p_number IN INTEGER)

    RETURN VARCHAR2

IS

    v_num   INTEGER := FLOOR (p_number / 1000);

BEGIN

    RETURN    CHR (FLOOR (v_num / (26 * 26)) + 65)

           || CHR (FLOOR (MOD (v_num, 26 * 26) / 26) + 65)

           || CHR (MOD (v_num, 26) + 65)

           || TO_CHAR (MOD (p_number, 1000), 'fm000');

END;

Open in new window

0
Comment
Question by:cyimxtck
  • 3
  • 2
5 Comments
 
LVL 41

Accepted Solution

by:
pcelba earned 500 total points
ID: 24388236
The function is here:
USE [Expres]

GO

/****** Object:  UserDefinedFunction [dbo].[format_sequence]    Script Date: 05/14/2009 20:53:12 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:		<Author,,Name>

-- Create date: <Create Date, ,>

-- Description:	<Description, ,>

-- =============================================

CREATE FUNCTION [dbo].[format_sequence] 

(

	-- Add the parameters for the function here

	@p_number int

)

RETURNS varchar(6)

AS

BEGIN

	-- Declare the return variable here

	DECLARE @ResultVar varchar(6)

	DECLARE @v_num int

	

	SET @v_num = FLOOR (@p_number / 1000)
 

    SET @ResultVar = CHAR (FLOOR (@v_num / (26 * 26)) + 65)

           + CHAR (FLOOR ((@v_num % (26 * 26)) / 26) + 65)

           + CHAR (@v_num % 26 + 65)

           + REPLACE(STR (@p_number % 1000, 3, 0),' ','0')
 

	-- Return the result of the function

	RETURN @ResultVar
 

END

Open in new window

0
 

Author Comment

by:cyimxtck
ID: 24388389
PERFECT and exactly what I needed.

Thank you so much!!!!
0
 

Author Closing Comment

by:cyimxtck
ID: 31581597
Perfect in every way!
0
 
LVL 41

Expert Comment

by:pcelba
ID: 24388419
The
USE [Expres]
GO
at the beginning should be replaced by your database name.

If you'll modify this function, you have to change
CREATE FUNCTION
to
ALTER FUNCTION
0
 

Author Comment

by:cyimxtck
ID: 24388431
I have already done that (so different than Oracle) but thank you so much!

B
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now