• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • 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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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