• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

sql server function to finding model number in description field

I'm looking for a function that will determine that a 'word' within a description is a model number and return the 'word'

To determine the model, the 'word' should either have:
1) 4 numbers
2) number and character combination, regardless of how many numbers

eg.:
"Canon PowerShot A230"

"A2300" is the model number because it has a character and number combination.
It likewise, would be a model if it was "2301"

I'm hoping that either someone has this code, or can write it quickly, and it would take me a while to figure out.
0
esak2000
Asked:
esak2000
  • 3
  • 2
2 Solutions
 
plusone3055Commented:
hmm well you could use PATINDEX for 4 numeric values
but the other one could be a little tough becuase there is no "set" standard
maybe witha substring if there was some standard
0
 
LowfatspreadCommented:
can we clarify  

you could have  

xxx 12 yyy a123       you want a123

xx 1234 www a123  you want 1234

what would you want returned if no model was found?

are words delimited  just by spaces?

when you say character do you actually mean an alphabetic character (a-z)  
or could it be any printable characters e.g. #12
0
 
LowfatspreadCommented:
something like this perhaps?


CREATE FUNCTION [dbo].[GetModelNumber]
(
    @String nvarchar(max)
   
)
RETURNS 
 nvarchar(max)

AS
BEGIN


    DECLARE @DelimiterIndex int,@modelnumber nvarchar(max)
    declare @outputtable table (val nvarchar(max),rowno int identity(1,1))
    SET @DelimiterIndex = -1
  
    WHILE (LEN(@String) > 0)
    BEGIN
        SET @DelimiterIndex = CHARINDEX(N' ', @String)
        IF (@DelimiterIndex = 0) AND (LEN(@String) > 0)
            BEGIN
                INSERT INTO @OutputTable VALUES (LTRIM(RTRIM(@String)))
                BREAK
            END
        IF (@DelimiterIndex > 1)
            BEGIN
                INSERT INTO @OutputTable VALUES (LTRIM(RTRIM(LEFT(@String, @DelimiterIndex - 1))))
                SET @String = RIGHT(@String, (LEN(@String) - @DelimiterIndex))
            END 
        ELSE SET @String = RIGHT(@String, (LEN(@String) - @DelimiterIndex))
    END
    
    Select top 1 @modelnumber=val from @outputtable
     where val like '[0-9][0-9][0-9][0-9]'
          or (val like '%[0=9]%' and val like '%[a-z]%'  )
     order by rowno

   return @modelnumber
    
END

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
esak2000Author Commented:
Lowfatspread,

Thank you very much for writing out the function. It looks like this would work, but I get an error:
Msg 536, Level 16, State 4, Line 1
Invalid length parameter passed to the RIGHT function.

I would like to accept your solution also,  do you know why I received the error?

In the mean time, I've written some code that words for me:

FUNCTION dbo.FindModelInDescription(
        @string                varchar(7999)
) RETURNS varchar(7999)
 
BEGIN
   
    DECLARE @newstring varchar(7999)
    DECLARE @num int
    DECLARE @code int
    DECLARE @numericFound int
   
    select @numericFound = 0
   
    SELECT @newstring = ''
    SELECT @num = 1
 
    WHILE @num < LEN(@string)+1
    BEGIN
        SET @code = ASCII(SUBSTRING(@String, @Num, 1))
        --- ascii: 48-57 (numbers 0-9)
        --- ascii: 65-90 (uppercase letters A-Z)
        --- ascii: 97-122 (lowercase letters a-z)
        IF (len(rtrim(SUBSTRING(@string, @num, 1))) = 0 and @num <> 1 and @numericFound = 0)
        BEGIN
                  set @newstring = ''
                  set @numericFound = 0
            END
           
            IF (len(rtrim(SUBSTRING(@string, @num, 1))) = 0 and  @numericFound = 1 and len(rtrim(@newstring)) > 0)
            BEGIN
                  break
            END
           
            SET @newstring = @newstring + SUBSTRING(@string, @num, 1)
             
            IF (@code between 48 and 57)
        BEGIN
                  set @numericFound = 1
        END
       
        SET @num = @num + 1
    END
   
   
    if (@numericFound = 1 and len(rtrim(@newstring )) > 0 and len(rtrim(@newstring)) >=4)
    begin
            set @newstring = replace(rtrim(ltrim(@newstring)),'#','')
      end
      else
      begin
     
            set @newstring = ''
      end
 
    RETURN @newstring
   
END
GO

Open in new window

0
 
LowfatspreadCommented:
sorry cant test at present ....

how big was your input string  should the declaration of @delimiterindex line 13 be bigint?

which version of sql server are you using?
0
 
esak2000Author Commented:
code working exactly how I wanted it to.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now