partial string select

Posted on 2012-08-20
Medium Priority
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 70

Expert Comment

by:Éric Moreau
ID: 38312454
you can use something like a string to table function: http://stackoverflow.com/questions/5257427/sql-server-string-to-table-function
LVL 11

Assisted Solution

by:Simone B
Simone B earned 400 total points
ID: 38312476
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

LIONKING earned 1600 total points
ID: 38312522
This is another version that works, using SubString.
Just replace with you Column Names and Table Name.

DECLARE @tmp as TABLE(Col1 VARCHAR(100))
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.

Author Closing Comment

ID: 38312716
Great Answer!  Thank You !

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 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