You must Sign In to post a response.
  • Category: SQL Server

    How to fetch a single value from multivalue attribute in sql

    I have one table called student.Table has 4 column.

    Student : Table

    Id Name RollNo SubjectCode
    1 Rani B0101 1,3,5,7,12
    2 Samidha B0102 1,2,4,6,12

    I want to find out name of student whose has subjectcode 2.

    Query:

    select name from student where charindex('2',subjectcode) > 0
    But problem occure when subjectcode contain 12.Because 12 contain 2.How to solve it?
  • #740033
    You may try like this


    Create table student(id int,name nvarchar(250),rollno nvarchar(250),Subjectcode nvarchar(250))


    Insert into student values (1,'Rani','B0101','1,3,5,7,12')
    Insert into student values (2,'Samidha','B0102','1,2,3,6,12')



    SELECT * FROM student WHERE charindex(',2,',subjectcode) >0



    Thanks & Regards
    G.Renganathan
    Nothing is mine ,Everything is yours!!!

  • #740035
    If you use the comma like this method..
    I think its will help u.

    select name from student where charindex(',2,',subjectcode) > 0

  • #740038
    Thanks G.Renganathan and Arulmurugan for ur help.
    I have just given u 2 entry.Subjectcode can be 2 only or 2,3,4.

  • #740040
    hello,
    try below code :

    declare @chartext as varchar(10)
    set @chartext='6' -- put the character which you want to search
    SELECT * FROM student WHERE charindex(@chartext+',',subjectcode) >0 or charindex(@chartext+',',subjectcode)=1 or charindex(','+@chartext,subjectcode)=(len(subjectcode)- (CHARINDEX(',',REVERSE(subjectcode))-1))


    If it helps you then Rate this.

    Best Regards,
    Manoranjan Sahoo
    http://www.dotnetsquare.com
    Blog: http://blog.msahoo.net

  • #740043
    Hi Pinky,

    Try this below query it will works on all conditions!



    CREATE TABLE StudentTable(ID INT,NAME VARCHAR(50),RollNo VARCHAR(50),SubjectCode VARCHAR(50))

    INSERT INTO StudentTable SELECT 1,'Raja','A1','1,2,3,4'
    INSERT INTO StudentTable SELECT 2,'Roja','B1','2,4,5,6'
    INSERT INTO StudentTable SELECT 3,'Rani','C1','1,2'
    INSERT INTO StudentTable SELECT 4,'Samidha','D1','11,12,13,14'
    INSERT INTO StudentTable SELECT 5,'Pooja','E1','2'

    Declare @A VARCHAR(50)
    Set @A=2

    SELECT * FROM StudentTable WHERE SubjectCode LIKE '%'+CONVERT(VARCHAR(50),','+@A)+
    (CASE WHEN RIGHT(CONVERT(VARCHAR(50),SubjectCode),LEN(@A))=CONVERT(VARCHAR(50),@A) THEN '' ELSE ',' END)+'%'
    OR (SubjectCode LIKE CONVERT(VARCHAR(50),@A+',')+'%') or (SubjectCode = CONVERT(VARCHAR(50),@A))

    Hope it will helps you! If means mark this post..

    Regards,
    Sri

  • #740071
    You can try the Query as below



    SELECT * FROM student WHERE
    subjectcode like '%,2,%'


    Thanks & Regards
    Anil Kumar Pandey
    Microsoft MVP, DNS MVM

  • #740414
    Thanks sri,for ur reply it really helped me.But it fails when SubjectCode is '5,22'


  • Sign In to post your comments