Solved

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

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 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

12 Experts available now in Live!

Get 1:1 Help Now