Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

sql server function to finding model number in description field

Posted on 2013-02-04
6
189 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

809 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