select query for column - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

select query for column

Author

Topic

mkool
Starting Member

25 Posts

Posted - 2008-03-13 : 13:07:53

i have one table called Healthmanagement
in that i have column HealthTitles in that there r so many rows..few of them r here:

HealthTitles
---------------------------------
Lung Health
Lung Health: General
Lung Health: Asperic Detailed Modified
Diseases
Diseases: in one generation
Attacks
Attacks: Health Related

i want in ouput all rows but which r with ':' i want after that part only and if the rows r without ':' then remain as it is:

so i want

HealthTitles
---------------------------------
Lung Health
General
Asperic Detailed Modified
Diseases
in one generation
Attacks
Health Related

how can i get that?
thanks for any help.

mkool
Starting Member

25 Posts

Posted - 2008-03-13 : 13:25:10

i tried like:
Select substring([HealthTitles
],1,charindex(':',[HealthTitles
])+1) as HealthTitles

FROM Healthmanagement

but i m getting

HealthTitles
---------------------------------
Lung Health
Lung Health:
Lung Health:
Diseases
Diseases:
Attacks
Attacks:

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-13 : 13:27:17

DECLARE @table TABLE (Disease varchar(100))

INSERT INTO @TABLE

SELECT 'Lung Health'
UNION ALL
SELECT 'Lung Health: General'
UNION ALL
SELECT 'Lung Health: Asperic Detailed Modified'
UNION ALL
SELECT 'Diseases'
UNION ALL
SELECT 'Diseases: in one generation'
UNION ALL
SELECT 'Attacks'
UNION ALL
SELECT 'Attacks: Health Related'

SELECT
CASE WHEN Disease like '%:%'
THEN LTRIM(SUBSTRING(Disease,PATINDEX('%:%',disease)+1,500))
ELSE Disease
END
FROM @table

Jim

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-13 : 13:29:25

One more version :)

DECLARE @Healthmanagement TABLE (HealthTitles VARCHAR(50))

INSERT @Healthmanagement
SELECT 'Lung Health'
UNION ALL SELECT 'Lung Health: General'
UNION ALL SELECT 'Lung Health: Asperic Detailed Modified'
UNION ALL SELECT 'Diseases'
UNION ALL SELECT 'Diseases: in one generation'
UNION ALL SELECT 'Attacks'
UNION ALL SELECT 'Attacks: Health Related'


SELECT 
	CASE 
		WHEN HealthTitles LIKE '%:%' THEN RIGHT(HealthTitles, LEN(HealthTitles) - (CHARINDEX(':', HealthTitles) + 1))
		ELSE HealthTitles
	END AS HealthTitles
FROM
	@Healthmanagement

mkool
Starting Member

25 Posts

Posted - 2008-03-13 : 13:44:23

thanks Lamprey and jimf..

both of you thanks..got both way correct results.
thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources