partial string select

Posted on 2012-08-20
Last Modified: 2012-08-20
I have a table column with format like Camera/2012/6 (string/year/month). I would like to make a query to seperate with / character like
select string as field1, year as field2, month as field3 from theTable

The string part may contaon a space, eg. "Other Stuff/2011/11"

How should I do it?  I'm using SQL Server 2005. Thanks.
Question by:minglelinch
    LVL 69

    Expert Comment

    by:Éric Moreau
    you can use something like a string to table function:
    LVL 11

    Assisted Solution

    by:Simone B
    I've tested this with your example, using a variable, and it returns the expected result. You'll want to use your own column and table names, of course:

    SELECT LEFT(YourColumn,CHARINDEX('/',YourColumn)-1) AS String,
    SELECT LEFT(RIGHT(YourColumn,LEN(YourColumn) - CHARINDEX('/',YourColumn)),CHARINDEX('/',RIGHT(YourColumn,LEN(YourColumn) - CHARINDEX('/',YourColumn)))-1) AS [Year],
    SELECT LEFT(REVERSE(YourColumn),CHARINDEX('/',REVERSE(YourColumn))-1) AS [Month]
    FROM YourTable
    LVL 13

    Accepted Solution

    This is another version that works, using SubString.
    Just replace with you Column Names and Table Name.

    DECLARE @tmp as TABLE(Col1 VARCHAR(100))
    INSERT INTO @tmp
    SELECT 'Some Text/2012/6'
    SELECT 'Another Text/2012/05'
    SELECT 'My Third Text/2012/10'
    SELECT 'JustText/2012/01'
    SELECT SUBSTRING(Col1,1,CHARINDEX('/',Col1,1)-1) AS  InitialText,
    SUBSTRING(Col1,CHARINDEX('/',Col1,1)+1,4) AS [Year],
    	CHARINDEX('/',SUBSTRING(Col1,CHARINDEX('/',Col1,1)+1,LEN(Col1)))+1,LEN(Col1)) AS [Month]
    FROM @tmp

    Open in new window

    Hope it helps.
    LVL 1

    Author Closing Comment

    Great Answer!  Thank You !

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    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.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now