SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with SQL Select Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cervuxs
Starting Member

3 Posts

Posted - 02/26/2014 :  09:04:52  Show Profile  Reply with Quote
Hi to all,

Hope somebody can help me. Right now I have a table where it has 5 columns (ie id,lessonname,status,content,username) now in the column username my program will dump a data that is like this (ie oipl,james). Now my question is what sql query can I do to get a result like this, lets say oipl only or james only. I have tried the like statement but it is just returning all the data.

Note:
here is a sample idea from my table.

Edited by - cervuxs on 02/26/2014 09:24:07

stepson
Constraint Violating Yak Guru

Romania
434 Posts

Posted - 02/26/2014 :  09:19:29  Show Profile  Reply with Quote


;with aCTE
AS(
	SELECT 1 AS id,'LES NAME1' AS lessonname,'STATUS1' AS [status],'CONTENT1' AS content,'oipl,james' AS username )


SELECT *
	
	, LEFT(USERNAME,CHARINDEX(',',username)-1) AS FNAME
	, RIGHT(USERNAME,LEN(USERNAME)-CHARINDEX(',',username)) AS SNAME
	
FROM 
	aCTE




S


sabinWeb MCP
Go to Top of Page

cervuxs
Starting Member

3 Posts

Posted - 02/26/2014 :  09:30:13  Show Profile  Reply with Quote
@sabinWeb wow great! it works, in this query can I also add where clause statement?
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
434 Posts

Posted - 02/26/2014 :  09:32:58  Show Profile  Reply with Quote
Yes,you can


sabinWeb MCP
Go to Top of Page

cervuxs
Starting Member

3 Posts

Posted - 02/26/2014 :  09:41:55  Show Profile  Reply with Quote
@sabinWeb like this?

SELECT *

, LEFT(USERNAME,CHARINDEX(',',username)-1) AS FNAME
, RIGHT(USERNAME,LEN(USERNAME)-CHARINDEX(',',username)) AS SNAME

FROM
tbllessons where fname = 'oipl'
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
434 Posts

Posted - 02/26/2014 :  10:00:18  Show Profile  Reply with Quote



SELECT
*
FROM
(

SELECT *

, LEFT(USERNAME,CHARINDEX(',',username)-1) AS FNAME
, RIGHT(USERNAME,LEN(USERNAME)-CHARINDEX(',',username)) AS SNAME

FROM
aCTE )Y

WHERE
y.fname = 'oipl'

[/CODE]



sabinWeb MCP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000