Solved

sql server function to finding model number in description field

Posted on 2013-02-04
6
190 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
[X]
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
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql Audit table 3 84
SQL Backup skipping a few tables 7 58
My Query is not giving correct result. Please help 5 55
Error in SSIS while executing  - Potential data loss 4 36
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 …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

726 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