By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,678 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

pick the values separately from a column field

100+
P: 147
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
Jan 27 '10 #1
Share this Question
Share on Google+
13 Replies


P: 53
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
Jan 27 '10 #2

ck9663
Expert 2.5K+
P: 2,878
Try using the LEN() function.

Happy Coding!!!

~~ CK
Jan 27 '10 #3

100+
P: 147
hi,

now i am trying like this :

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



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
Jan 28 '10 #4

Uncle Dickie
P: 67
Will this help you:

Expand|Select|Wrap|Line Numbers
  1. SELECT        substring(Course,0,6)
  2.             ,count(substring(Course,0,6))
  3. FROM        dbo.R_test
  4. GROUP BY    substring(Course,0,6)
?
Jan 28 '10 #5

P: 53
Hi,

Hope this will help.


Expand|Select|Wrap|Line Numbers
  1. CAST(SUBSTRING(b.crseno,1,5,) + ' - ' + CAST(COUNT(SUBSTRING(b.crseno,1,5,)) AS VARCHAR(2)) AS VARCHAR(10)) AS crse
  2.  
Cheers

Leon
Jan 28 '10 #6

100+
P: 147
hi,

No its not giving the count.....please tell some other way.
Jan 28 '10 #7

Uncle Dickie
P: 67
which solution?
Leon's or mine?
Jan 28 '10 #8

P: 53
Are these columns you are referencing within the statement:

Expand|Select|Wrap|Line Numbers
  1. 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 
  2.  
within a single table or view?

Cheers

Leon
Jan 28 '10 #9

100+
P: 147
hi,

not a single table ....i am using join.
Jan 28 '10 #10

P: 53
Hi

So something like

Expand|Select|Wrap|Line Numbers
  1. SELECT a.rollno, CAST(SUBSTRING(b.crseno,1,5) + ' - ' +
  2. CAST(COUNT(SUBSTRING(b.crseno,1,5)) AS VARCHAR(2)) AS VARCHAR(10)) AS crse
  3. FROM a INNER JOIN
  4. b ON a.crsid = b.crsid
  5. WHERE (a.rollno='CE08B013')
  6.  
  7. GROUP BY a.rollno,b.crseno
  8. HAVING (CAST(COUNT(SUBSTRING(b.crseno,1,5)) AS INT)>1)
  9. order by b.crseno
  10.  
Hope this helps

Cheers

Leon
Jan 28 '10 #11

ck9663
Expert 2.5K+
P: 2,878
Does the value on your table include the number on the left? The "1." and "2." ? Or just the actual course code?

~~ CK
Jan 28 '10 #12

100+
P: 147
hi,

No,actual course code .I cant able to pick the count of the substring.......please help me out
Jan 29 '10 #13

ck9663
Expert 2.5K+
P: 2,878
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. select * from course where len(course_code) = 5
  3.  
  4.  
Is that what you mean?

~~ CK
Jan 29 '10 #14

Post your reply

Sign in to post your reply or Sign up for a free account.