Help required in SQL 2005

Question

Help required in SQL 2005

Syed Aun Shah

Newbie

Points: 4
More actions
April 22, 2009 at 5:18 am

#202397

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Check out LEFT() and CHARINDEX() in Books Online.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

hayzer Say Hey Kid Points: 713 More actions · Answer 2

You might want to add sample data we can work with so we can clearly understand what you want..

Syed Aun Shah Newbie Points: 4 More actions · Answer 3

sample data is as:

table name: Student

Field Name: Stuent ID

Data in the field looks like:

1*10

11*12

1212*11

1234*11

12345*12

433444*10

-------------------

now what i want is to fetch no.s from the left side of *. as i already mentioned the above mentioned student_id is basically composition of: unique# for student + *(for id spliter) + department id( department in which student is learning) i.e. 11*12

thanks for your prompt reply 🙂 please help me.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Syed Aun Shah (4/22/2009)
sample data is as:
table name: Student
Field Name: Stuent ID
Data in the field looks like:
1*10
11*12
1212*11
1234*11
12345*12
433444*10

Please read the link below, and provide table scripts and sample data, which will look something like this:

DROP TABLE #NullSample

CREATE TABLE #NullSample (ID INT, Something VARCHAR(15))

INSERT INTO #NullSample (ID, Something)

SELECT 1, 'a value' UNION ALL

SELECT 2, 'another value' UNION ALL

select 3, null

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Syed Aun Shah Newbie Points: 4 More actions · Answer 5

Create Table

USE [MIS]

GO

/****** Object: Table [dbo].[tbl_Student] Script Date: 04/22/2009 18:39:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tbl_Student](

[Student_ID] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

insert data:

insert into tbl_student (student_id) values ('1*10' )

insert into tbl_student (student_id) values ('11*12' )

insert into tbl_student (student_id) values ('1212*11' )

insert into tbl_student (student_id) values ('1234*11' )

insert into tbl_student (student_id) values ('12345*12' )

insert into tbl_student (student_id) values ('433444*10' )

-----------

and i want to fetch values on the left side of *.

WayneS SSC Guru Points: 95460 More actions · Answer 6

select LEFT(Student_ID, CharIndex('*', Student_id)-1) [Real_Student_ID],

SUBSTRING(Student_ID, CharIndex('*', Student_ID)+1, len(Student_ID)) [Real_Department_ID]

from tbl_student

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Syed Aun Shah Newbie Points: 4 More actions · Answer 7

Syed Aun Shah

Newbie

Points: 4

April 23, 2009 at 12:08 am

#982668

Thank you very much .. it worked