pick the values separately from a column field

[santhanalakshmi]

hi,

I have a field named as course.

in that course field i have the values like this :

course
1. AM110
2. AM1100
3. CY101
4. CY1010


first ,i want to pick only 5 digit course :

my output should be :

1. AM110
2. CY101


please help me out...its so urgent.i try using substring but its waste.thanks in advance

[Echidna]

If you have tried something like

SELECT Column1, SUBSTRING([Course], 1, 5) AS course
FROM Tablename

You could always try

SELECT Column1, CAST( [course] AS varchar(5)) AS course
FROM TableName

they both produce the same resultset for me.

Hope this helps

Cheers

Leon

[ck9663]

Try using the LEN() function.

Happy Coding!!!

~~ CK

[santhanalakshmi]

hi,

now i am trying like this :

select a.rollno,count( substring(b.crs eno,1,5)) as crse from ucsrgdet a,corsemst b where a.rollno='CE08B 013' and a.crseid=b.crse id group by a.rollno,b.crse no having count(substring (b.crseno,1,5)) >1 order by b.crseno

But the COUNT function is not working for the SUBSTRING.

in my table course :
1 . AM110
2. AM1100

by using string function :
output :
1. AM110
2. AM110


and at the same time,when i am trying COUNT FUNCTION for the SUBSTRING ...its not giving me the count properly.

i want the OUTPUT AM110 - 2 ...please help me out .thanks

[Uncle Dickie]

Will this help you:

SELECT		substring(Course,0,6)
			,count(substring(Course,0,6))
FROM		dbo.R_test
GROUP BY	substring(Course,0,6)

?

[Echidna]

Hi,

Hope this will help.

CAST(SUBSTRING(b.crseno,1,5,) + ' - ' + CAST(COUNT(SUBSTRING(b.crseno,1,5,)) AS VARCHAR(2)) AS VARCHAR(10)) AS crse

Cheers

Leon

[santhanalakshmi]

hi,

No its not giving the count.....pleas e tell some other way.

[Uncle Dickie]

which solution?
Leon's or mine?

[Echidna]

Are these columns you are referencing within the statement:

select a.rollno,count(substring(b.crseno,1,5)) as crse from ucsrgdet a,corsemst b where a.rollno='CE08B013' and a.crseid=b.crseid group by a.rollno,b.crseno having count(substring(b.crseno,1,5))>1 order by b.crseno

within a single table or view?

Cheers

Leon

[santhanalakshmi]

hi,

not a single table ....i am using join.

[Echidna]

Hi

So something like

SELECT a.rollno, CAST(SUBSTRING(b.crseno,1,5) + ' - ' +
CAST(COUNT(SUBSTRING(b.crseno,1,5)) AS VARCHAR(2)) AS VARCHAR(10)) AS crse
FROM a INNER JOIN
b ON a.crsid = b.crsid
WHERE (a.rollno='CE08B013')

GROUP BY a.rollno,b.crseno
HAVING (CAST(COUNT(SUBSTRING(b.crseno,1,5)) AS INT)>1)
order by b.crseno

Hope this helps

Cheers

Leon

[ck9663]

Does the value on your table include the number on the left? The "1." and "2." ? Or just the actual course code?

~~ CK

[santhanalakshmi]

hi,

No,actual course code .I cant able to pick the count of the substring...... .please help me out

[ck9663]

So you have a table called COURSE with the following value:


course_code
----------------
AM110
AM1100
CY101
CY1010

You want to get all those course with 5-character length.

select * from course where len(course_code) = 5

Is that what you mean?

~~ CK