#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    1
    Rep Power
    0

    Select on variable length field question


    Hi,

    I have a list of item numbers of varying lengths (13 - 19), and only wish to select those items that match my criteria of the first 5 characters and the last 4 characters

    sample item file:

    Item number

    5567094567891234
    55670776761234
    556717887871234
    5568276767671234
    5567078687676
    5567076761234

    search criteria:
    first 5 characters = '55670'
    last 4 characters = '1234'

    desired result:
    5567094567891234
    55670776761234
    5567076761234

    yes, I'm expecting it to be a response time pig.

    any help would be appreciated. thanks!
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Location
    Katy, Texas
    Posts
    495
    Rep Power
    199
    PHP Code:
    create table t1                                   
       
    c1  varchar(20not null ) ;                 
                                                      
    insert into t1 (c1values ('5567094567891234') ; 
    insert into t1 (c1values ('55670776761234') ;   
    insert into t1 (c1values ('556717887871234') ;  
    insert into t1 (c1values ('5568276767671234') ; 
    insert into t1 (c1values ('5567078687676') ;    
    insert into t1 (c1values ('5567076761234') ;    
                                                      
    select from t1                                  
    where left
    (c1,5) = '55670'                        
    and                                               
    right(rtrim(c1),4) = '1234' 
    Do you agree? Disagree? And remember, it's all about the reputation power...

IMN logo majestic logo threadwatch logo seochat tools logo