how to find an integer value from varchar

Question

Post reply

how to find an integer value from varchar

Raghavendra-499237

Default port

Points: 1435
More actions
November 26, 2007 at 3:36 am

#179414

Hi all,
I have 2 tables Document and Department. Document table will contain Doc_id,Doc_name columns. Department contains Dept_id, Dept_Desc and document_list (which stores Doc_id in a comma seperated manner) columns.
sample data in my tables will be like this (it may help you to get into the problem)
Document table
Doc_id Doc_name
1 aaa
2 bbb
3 ccc
Department table
Dept_id Dept_Descdocument_list
1 xxx1,2
2 yyy2,3
3 zzz1,2,3
now i want to display all departments for a given document id (for example if document id is 3 then departments will be yyy and zzz).
can someone help me to find the solution?
thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

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

allen davidson SSCertifiable Points: 7042 More actions · Answer 1

Hi Raghavendra

Your second table is not normalised. The comms seperated document list is the problem.

You should remove the document list from the department table and create a third table to hold department id and a single document id like this.

dept_id doc_id

1 1

1 2

2 2

2 3

3 1

3 2

3 3

You then have a simple join to say which depts have which docs.

Allen

Raghavendra-499237 Default port Points: 1435 More actions · Answer 2

Hi Allen,

thanks for your suggestion, now i did this using like operator, will it affect the performance of sql server if i use like operator?

karthik M One Orange Chip Points: 29139 More actions · Answer 3

just try out this code

Select Detp_Desc

from document a ,department b

where a.doc_id = right(document_list,1)

karthik

allen davidson SSCertifiable Points: 7042 More actions · Answer 4

Hi Raghavendra

Not sure I understand how you did it using LIKE.

Regarding performance it really depends on your SQL query and the indexes you have on your table.

Allen

Raghavendra-499237 Default port Points: 1435 More actions · Answer 5

Hi, All

I have created a stored procedure like this.

create procedure sp_getRelatedDepartments

(

@documentid int

)

as

SELECT * FROM Department WHERE document_list=@documentid OR document_list like '%,'+@documentid OR document_list like '%,'+@documentid+',%' OR document_list like @documentid+',%'

allen davidson SSCertifiable Points: 7042 More actions · Answer 6

But will your stored proc differentiate between 1 and 11 ?

Raghavendra-499237 Default port Points: 1435 More actions · Answer 7

Raghavendra-499237

Default port

Points: 1435

November 26, 2007 at 5:01 am

#753959

yes , it will differentiate 1 and 11

karthik M One Orange Chip Points: 29139 More actions · Answer 8

Ragavendra,

how are you saying it will differentiate ?;) I think it should not.

Going a little detail , basically you should avoid using 'OR' strategy in your queries. It would produce 'Cartesian Product'.

karthik

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 9

Unfortunately, using the LIKE operator always affects query performance. For one, if you use the wildcard on both sides of the search syntax, then you can inadvertantly generate tablescans.

The bigger the table, the bigger the performance hit. You may not notice it at first, but as your database grows, using the LIKE operator can cause future significant problems. So it's best to redesign your tables now as previously suggested (if you can) to avoid the problem.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.