locked
SQL Query and Substring? RRS feed

  • Question

  • Hi,

    I'm trying to do some parsing and struggling a bit.  I have a table ("Users") and about 150 rows or so of data.  One of the columns in the "Users" table  is called "row_num".   I need to use the values from "row_num" column  to query another table to isolate the records I need.

    I first query and return a list of users, get the the value from the "row_num" column for each user.  I'd then like to query another table to get that  user's specific images.  The images have a naming pattern.   Please see the values below:

    Table :  Images
    Column Name: Image_Name
    Some values below

    2b.jpg
    2c.jpg
    20a.jpg
    20b.jpg
    32a.jpg
    45a.jpg
    45b.jpg

    If the row_num is = 2, then return ONLY  2b.jpg and 2c.jpg

    If the row_num is = 20, then return ONLY 20a.jpg and 20b.jpg

    If the row_num is = 32, then return ONLY 32a.jpg

    How can I write a query that will do this.  I can grab all my users fine and the row_num, but struggling on how to deal with the naming pattern and parsing the names of the images and comparing them against my "row_num"

    This is a one time process so I don't need to be super elegant here, just want something that makes sense...

    Any help appreciated...

    Thanks..

    Monday, February 18, 2013 9:11 AM

Answers

  • Just create a UDF to return the numbers from the string. below is sample UDF

    CREATE FUNCTION dbo.udf_GetNumeric
    (@strAlphaNumeric VARCHAR(256))
    RETURNS VARCHAR(256)
    AS
    BEGIN
    DECLARE @intAlpha INT
    SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
    BEGIN
    WHILE @intAlpha > 0
    BEGIN
    	SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
    	SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
    END
    END
    RETURN ISNULL(@strAlphaNumeric,0)
    END
    GO

    and now you can achieve the requirement using below sample code

    Create table #test (imagename varchar(200))
    
    
    insert into #test select '2b.jpg'
    insert into #test select '2c.jpg'
    insert into #test select '20a.jpg'
    insert into #test select '20b.jpg'
    insert into #test select '32a.jpg'
    
    
    
    select * from #test where dbo.udf_GetNumeric(imagename) ='2'
    
    drop table #test
    
    
    
    
    


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    • Proposed as answer by Satheesh Variath Monday, February 18, 2013 10:35 AM
    • Marked as answer by Westside2008 Monday, February 18, 2013 6:45 PM
    Monday, February 18, 2013 9:24 AM
  • SELECT i.*, LEFT(Image_Name,PATINDEX('%[^0-9]%', Image_Name) - 1)  as num 
    FROM Images i INNER JOIN Users u On  i.num=u.row_num
    

    • Proposed as answer by Naarasimha Monday, February 18, 2013 4:14 PM
    • Marked as answer by Westside2008 Monday, February 18, 2013 6:47 PM
    Monday, February 18, 2013 3:02 PM

All replies

  • Convert the number for varchar and use the LIKE operator:

    USE [AdventureWorks2012]
    GO
    
    DECLARE @keyword varchar(20) = 'pedal';
    SELECT [ThumbnailPhotoFileName]
    FROM [Production].[ProductPhoto]
    WHERE [ThumbnailPhotoFileName] like (@keyword+'%');
    GO
    -- pedal_small.gif
    


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Monday, February 18, 2013 9:20 AM
  • Just create a UDF to return the numbers from the string. below is sample UDF

    CREATE FUNCTION dbo.udf_GetNumeric
    (@strAlphaNumeric VARCHAR(256))
    RETURNS VARCHAR(256)
    AS
    BEGIN
    DECLARE @intAlpha INT
    SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
    BEGIN
    WHILE @intAlpha > 0
    BEGIN
    	SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
    	SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
    END
    END
    RETURN ISNULL(@strAlphaNumeric,0)
    END
    GO

    and now you can achieve the requirement using below sample code

    Create table #test (imagename varchar(200))
    
    
    insert into #test select '2b.jpg'
    insert into #test select '2c.jpg'
    insert into #test select '20a.jpg'
    insert into #test select '20b.jpg'
    insert into #test select '32a.jpg'
    
    
    
    select * from #test where dbo.udf_GetNumeric(imagename) ='2'
    
    drop table #test
    
    
    
    
    


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    • Proposed as answer by Satheesh Variath Monday, February 18, 2013 10:35 AM
    • Marked as answer by Westside2008 Monday, February 18, 2013 6:45 PM
    Monday, February 18, 2013 9:24 AM
  • http://www.sommarskog.se/dyn-search-2005.html

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, February 18, 2013 9:31 AM
    Answerer
  • Hi, try this

    declare @table2 table (rownum varchar(max))
    insert into @table2
    select '2b.jpg' union
    select '2c.jpg' union
    select '20a.jpg' union
    select '20b.jpg' union
    select '32a.jpg'union
    select '45a.jpg' union
    select '45b.jpg'
    
    select * from @table2 a
    where replace(rownum,right(rownum,5),'') = 45
    


    Dineshkumar Please "Mark as Answer" if my post answers your question or "Vote as Helpful" if it helps you

    Monday, February 18, 2013 11:16 AM
  • SELECT i.*, LEFT(Image_Name,PATINDEX('%[^0-9]%', Image_Name) - 1)  as num 
    FROM Images i INNER JOIN Users u On  i.num=u.row_num
    

    • Proposed as answer by Naarasimha Monday, February 18, 2013 4:14 PM
    • Marked as answer by Westside2008 Monday, February 18, 2013 6:47 PM
    Monday, February 18, 2013 3:02 PM
  • IF OBJECT_ID('tempdb..#test') IS NOT NULL 
    BEGIN 
                DROP TABLE #test 
    END 
    Create table #test (imagename varchar(200))
    insert into #test select '2b.jpg'
    insert into #test select '2c.jpg'
    insert into #test select '20a.jpg'
    insert into #test select '20b.jpg'
    insert into #test select '32a.jpg'
    SELECT * FROM #test
    IF OBJECT_ID('tempdb..#test1') IS NOT NULL 
    BEGIN 
                DROP TABLE #test1
    END 
    Create table #test1 (users varchar(20),row_num INT)
    insert into #test1 select 'abc',2
    insert into #test1 select 'def',32
    insert into #test1 select 'ghi',20
    insert into #test1 select 'def',32
    insert into #test1 select 'abc',2
    insert into #test1 select 'ghi',20
    SELECT * FROM #test1
    ;WITH CTE AS
    (
    SELECT *,PATINDEX('%[^0-9]%',imagename) AS pos,SUBSTRING(imagename,1,PATINDEX('%[^0-9]%',imagename)-1) AS numer 
    FROM #test a
    )
    SELECT DISTINCT a.imagename,b.users
    FROM CTE a
    INNER JOIN #test1 b
     ON a.numer=b.row_num


    Narsimha

    Monday, February 18, 2013 4:15 PM
  • Thanks for all the replies.  I ended up utilizing the function that Iam Rakesh put together and that worked the first time..

    I tried your solution too and it approached it a slightlydifferent way, but that option worked for me too so thanks!

    Definiately appreciate all the replies from everyone.  I've never used PATINDEX so thanks for that.  I need to read up on the docs a bit...

    Monday, February 18, 2013 6:50 PM