Web Development

Ask something
Question Locked

Help Required in SQL2005

0 helpful
2 replies

by aunsyed

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello
Can anybody help me in this scnerio:
i have a field in my database as student id, which is composition of student_id and department. i want to fetch ids from this. i.e.
student id: 1111*12, details: (1111(student id) *(spliter) 12(departmentid)
i want to have such a query which may pick 1111 from the student id field. length of student id may vary i..e it can be 1 or 11 or 111 etc.

waiting for help.

2 replies

by tony hopkinson

the charindex function will return where the * is, then the Left and Right functions will get you the parts.

e.g

select Left([StudentID,CharIndex('*',[StudentID]) - 1) as StudentID, Right([StudentID,DataLength([StudentID]) - CharIndex('*',[StudentID])) as DeptID
From MyTable

Messy isn't it! Personally I'd bust it in to two columns, add a compound key and then simply bolt them together if required.

This discussion is closed to new replies.