Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with SQL Select Query

Author  Topic 

cervuxs
Starting Member

3 Posts

Posted - 2014-02-26 : 09:04:52
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.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-26 : 09:19:29
[code]

;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

[/code]


S


sabinWeb MCP
Go to Top of Page

cervuxs
Starting Member

3 Posts

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

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-26 : 09:32:58
Yes,you can


sabinWeb MCP
Go to Top of Page

cervuxs
Starting Member

3 Posts

Posted - 2014-02-26 : 09:41:55
@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
Aged Yak Warrior

545 Posts

Posted - 2014-02-26 : 10:00:18
[code]


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
   

- Advertisement -