Solved

sql server function to finding model number in description field

Posted on 2013-02-04
6
185 Views
Last Modified: 2013-03-12
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
Comment
Question by:esak2000
  • 3
  • 2
6 Comments
 
LVL 22

Expert Comment

by:plusone3055
ID: 38851401
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38851481
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 500 total points
ID: 38851572
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Accepted Solution

by:
esak2000 earned 0 total points
ID: 38851638
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38851705
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
 

Author Closing Comment

by:esak2000
ID: 38976830
code working exactly how I wanted it to.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 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

18 Experts available now in Live!

Get 1:1 Help Now