partial string select

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.
LVL 1
minglelinchAsked:
Who is Participating?
 
LIONKINGConnect With a Mentor Commented:
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'
UNION ALL
SELECT 'Another Text/2012/05'
UNION ALL
SELECT 'My Third Text/2012/10'
UNION ALL
SELECT 'JustText/2012/01'


SELECT SUBSTRING(Col1,1,CHARINDEX('/',Col1,1)-1) AS  InitialText,
SUBSTRING(Col1,CHARINDEX('/',Col1,1)+1,4) AS [Year],
SUBSTRING(SUBSTRING(Col1,CHARINDEX('/',Col1,1)+1,LEN(Col1)),
	CHARINDEX('/',SUBSTRING(Col1,CHARINDEX('/',Col1,1)+1,LEN(Col1)))+1,LEN(Col1)) AS [Month]
FROM @tmp

Open in new window


Hope it helps.
0
 
Éric MoreauSenior .Net ConsultantCommented:
you can use something like a string to table function: http://stackoverflow.com/questions/5257427/sql-server-string-to-table-function
0
 
Simone BConnect With a Mentor Senior E-Commerce AnalystCommented:
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
0
 
minglelinchAuthor Commented:
Great Answer!  Thank You !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.