Post reply

Part of a String

  • October 18, 2010 at 1:37 pm

    #229783

    Hello Everyone

    I am working on using part of a string to name the related graphic file.

    This is an example of what I have:

    1001W-Large.jpg

    or

    1778SMB-Small.jpg

    There are no set number of characters before the hyphen, but all names have a hyphen.

    What can I use to select only all characters to the left side of the hyphen? So that the value is:

    1001W

    or

    1778SMB

    I have tried SubString and CharIndex, but cannot seem to get it exact.

    Thanks in advance

    Andrew SQLDBA

  • October 18, 2010 at 1:50 pm

    #1238708

    Is this what you asked for?

    SELECT SUBSTRING(@name, 1, CHARINDEX('-', @name) -1)

    OR

    SELECT LEFT(@name, CHARINDEX('-', @name) -1)

  • October 18, 2010 at 1:56 pm

    #1238713

    this works, andrew, but i've got an extra +1 and -1 in the mis that can be cleaned up for the "Descript" column:

    /*

    BaseFileName Descriptor Extension

    1001W Large jpg

    1778SMB Small jpg

    ADifferentFilename Small jpeg

    NoDashes NoDashes jpeg

    */

    with myCTE as (SELECT '1001W-Large.jpg' As TheFilename UNION ALL

    SELECT '1778SMB-Small.jpg' UNION ALL

    SELECT 'ADifferentFilename-Small.jpeg' UNION ALL

    SELECT 'NoDashes.jpeg')

    SELECT

    CASE

    WHEN CHARINDEX('-',TheFileName) > 0

    THEN LEFT(TheFilename,CHARINDEX('-',TheFileName) - 1) --minus one to REMOVE the dash

    ELSE LEFT(TheFilename,CHARINDEX('.',TheFileName) - 1)

    END AS BaseFileName,

    CASE

    WHEN CHARINDEX('-',TheFileName) > 0

    THEN SUBSTRING(TheFilename,CHARINDEX('-',TheFileName) + 1,CHARINDEX('.',TheFileName) + 1 - (CHARINDEX('-',TheFileName) + 1) -1 )

    ELSE SUBSTRING(TheFilename,1,CHARINDEX('.',TheFileName) + 1 - (CHARINDEX('-',TheFileName) + 1) -1)

    END AS Descriptor,

    REVERSE(LEFT(REVERSE(TheFilename),CHARINDEX('.',REVERSE(TheFilename)) - 1)) AS Extension

    FROM myCTE

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 18, 2010 at 1:57 pm

    #1238716

    Thanks WildCat

    I figured it out right after another coffee.

    I will keep your sample code for future queries.

    Thanks again

    Andrew SQLDBA

  • October 24, 2010 at 12:32 pm

    #1241356

    AndrewSQLDBA (10/18/2010)

    Thanks WildCat

    I figured it out right after another coffee.

    I will keep your sample code for future queries.

    Thanks again

    Andrew SQLDBA

    Two way street here, Andrew... can you please post what it is that you figure out? Thanks.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 24, 2010 at 1:20 pm

    #1241364

    This is what I used to get the value that I needed

    DECLARE @ProductName varchar(50)

    SET @ProductName = '10494B-XL-Large.jpg'

    SELECT SUBSTRING(@ProductName, 1, CHARINDEX('-', @ProductName) -1)

    And the result is

    10494B

    Andrew SQLDBA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply