Solved

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

Posted on 2009-05-14
5
228 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 42

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 42

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

828 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