Optimize query in sql

Question

Can anyone suggest someways to improve this function?I just want to display number in numeric when input is given in string(below 100)?

I'm just a beginner,so help is highly appreciated, Thanks in advance

CREATE FUNCTION dbf_strinf_to_num (@word as varchar(50))
    RETURNS varchar(30)

AS
    BEGIN            
    DECLARE @number varchar(30)
    DECLARE @cropword varchar (20) 
    SELECT @cropword = LEFT(@word,5)
    DECLARE @namecount numeric(18,6) 
    SELECT @namecount =  LEN(@word+ ';') - LEN(REPLACE(@word,' ','')) 
        IF @namecount IN(1,2)
            BEGIN
            IF @namecount = 1
              BEGIN
                SELECT @number =                                                 
                    (
                      SELECT CASE 
                        WHEN @cropword LIKE '%ONE%'
                            THEN 1
                        WHEN @cropword LIKE '%TWO'
                            THEN 2
                        WHEN @cropword LIKE '%REE'
                            THEN 3
                        WHEN @cropword LIKE '%OUR'
                            THEN 4
                        WHEN @cropword LIKE '%IVE'
                            THEN 5
                        WHEN @cropword LIKE '%SIX'
                            THEN 6
                        WHEN @cropword LIKE '%VEN'
                            THEN 7
                        WHEN @cropword LIKE '%GHT'
                            THEN 8
                        WHEN @cropword LIKE '%INE'
                            THEN 9
                        WHEN @word like '%E%N'
                            THEN 11
                        WHEN @word like '%T%E'
                            THEN 12
                        WHEN @word like '%T%N'
                            THEN 13
                        WHEN @word like '%FO%N'
                            THEN 14
                        WHEN @word like '%F%N'
                            THEN 15
                        WHEN @word like '%S%N'
                            THEN 16
                        WHEN @word like '%SE%N'
                            THEN 17
                        WHEN @word like '%EI%N'
                            THEN 18
                        WHEN @word like '%N%N'
                            THEN 19
                        ELSE 0 END 
                    )
               END
            ELSE
                IF @namecount = 2
                 BEGIN
                   --SELECT @number = dbf_strinf_to_num1(@word) 
                   SELECT @cropword = LEFT(@word,5)
                   SELECT @number =                                                 
                    (
                      SELECT CASE 
                        WHEN @cropword LIKE '%ONE%'
                            THEN 1
                        WHEN @cropword LIKE 'TWE%'
                            THEN 2
                        WHEN @cropword LIKE 'THIR%'
                            THEN 3
                        WHEN @cropword LIKE 'FOUR%'
                            THEN 4
                        WHEN @cropword LIKE 'FIF%'
                            THEN 5
                        WHEN @cropword LIKE 'SIX%'
                            THEN 6
                        WHEN @cropword LIKE 'SEVEN%'
                            THEN 7
                        WHEN @cropword LIKE 'EIGHT%'
                            THEN 8
                        WHEN @cropword LIKE 'NIN%'
                            THEN 9

                        ELSE 0 END 
                    )
                    select @number = @number + dbo.dbf_strinf_to_num(RIGHT(@word,3))
                 END
              END
        ELSE
            BEGIN
               SELECT @number = 'Error! PLease enter valid number(1-100)'
            END
    RETURN (@number)

END

Unfortunately SQL Server has not spelled format like Oracle SELECT TO_CHAR (TO_DATE (TRUNC (15321), 'J'), 'JSP') FROM DUAL; — Lukasz Szozda
– Lukasz Szozda, Commented Aug 21, 2015 at 12:19
Yes,that's really unfortunate for me.Hope they will add this in future. — Azarudeen
– Azarudeen, Commented Aug 21, 2015 at 12:22

Hercule · Accepted Answer · 2015-08-21 12:47:29Z

2

The easiest way to do so, is to create a table with two column. First one containing the number in word and the second column the equivalent in number.

Create table NumberToWord
(
    NumberText varchar(50),
    NumberInt int
)

Once the table populated, you can make a simple join to get the correct number translation.

Another solution for number above 100 at SQL Central : http://www.sqlservercentral.com/Forums/Topic794134-149-1.aspx

Hope it helps.

edited Aug 21, 2015 at 12:47

answered Aug 21, 2015 at 12:11

Hercule

1861 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Azarudeen Over a year ago

Thanks.That's really very helpful, Instead of create table is there is any function which will auto increment the string in sql.

Azarudeen Over a year ago

It will surely helpful for below 100,But when i try to get below 1000 it may not helpful.

Azarudeen · Accepted Answer · 2015-08-21 12:29:54Z

0

Create table NumberToWord
(
    NumberText varchar(50),
    NumberInt int identity(1,1)
)

I just add identity to that and it works better

answered Aug 21, 2015 at 12:29

Azarudeen

732 silver badges13 bronze badges

Collectives™ on Stack Overflow

Optimize query in sql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related