CODEWITHSUNDEEP
sqlsql-serversql-function
Faraz Ahmed
Faraz Ahmed

Reputation: 1607

return two character from function containing numbers and alphabet in SQL

I want to make function which will return two character based on the condition.

let say I have a table tbl_Dummy

CREATE TABLE [dbo].[tbl_Dummy](
    [Id] [varchar](2) NOT NULL,
    [Name] [varchar](30) NOT NULL
)

if Max value of Id in tbl_dummy is 01, then it returns 02, and so on, when it passes to 09, then it returns 0A til 0Z, after that it will return 10 and so on.

I have done this, but this is not working in my scenerio

create FUNCTION dbo.GenerateValue ()
RETURNS VARCHAR(250)
AS BEGIN
    DECLARE @counter int = 1;
    DECLARE @Work VARCHAR(2)
    DECLARE @temp VARCHAR(2)
    DECLARE @tempW VARCHAR(2)
    declare @value int 

    select @Work = MAX(id) from tbl_Dummy
    WHILE @counter <= DATALENGTH(@Work)
   BEGIN
   SELECT  @temp = ASCII(SUBSTRING(@Work, @counter, 1))
   SET @counter = @counter + 1
   if @temp >= '48' and @temp <= '56' or @temp >= '65' and @temp <= '89'
   begin
        select @value = CONVERT(INT, @temp) 
        set @value = @temp + 1
   end
   else if @temp = '57'
        set @value = 'A'
    else if @temp = '90'
        set @tempW = '0'
   set @tempW +=   CHAR(ASCII(SUBSTRING(@Work, @counter, 1)))
   END
    RETURN @work
END

Upvotes: 1

Views: 68

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Instead of getting the MAX(Id) every time, you should add an IDENTITY column in your table and a computed column to compute the correct Id.

CREATE TABLE tbl_Dummy(
    TempId  INT IDENTITY(1, 1),
    Id      AS ISNULL(dbo.GenerateValue(TempId),'') PERSISTED,
    Name    VARCHAR(30) NOT NULL
)

This way, once you insert a row in tbl_Dummy you don't always have to compute for the latest Id. The TempId will give that for you. As for how to compute the desired Id, here is one way without looping:

CREATE FUNCTION dbo.GenerateValue (@N INT)
RETURNS CHAR(2) WITH SCHEMABINDING AS
BEGIN
RETURN 
(
    SELECT returnStr =      
        SUBSTRING(Str1, (@N/36) % LEN(Str1) + 1, 1) + --LeftMost 
        SUBSTRING(Str1, (@N/1) % LEN(Str1) + 1, 1) --RightMost
    FROM (
        SELECT '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    ) d(Str1)
);

Sample Usage:

INSERT INTO dbo.tbl_Dummy(Name)
SELECT TOP 20
    SomethingElse = 'Random' + CONVERT(VARCHAR(10), ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))
FROM sys.all_columns ac1

SELECT * FROM dbo.tbl_DUmmy

Result:

TempId      Id   Name
----------- ---- ------------------------------
1           01   Random1
2           02   Random2
3           03   Random3
4           04   Random4
5           05   Random5
6           06   Random6
7           07   Random7
8           08   Random8
9           09   Random9
10          0A   Random10
11          0B   Random11
12          0C   Random12
13          0D   Random13
14          0E   Random14
15          0F   Random15
16          0G   Random16
17          0H   Random17
18          0I   Random18
19          0J   Random19
20          0K   Random20

Reference:

  • An answer by Jeff Moden on a similar problem:

Upvotes: 2

Related Questions