i have a problem

Question

Post reply

i have a problem

sukhoi971

SSCarpal Tunnel

Points: 4833
More actions
December 8, 2004 at 12:46 am

#182967

i have a problem,
i need a query to select from a table where only
policycode has "/" this character in the string
see the policycode=001
when it gets endorse, it gets changed to 001/02
and if it again gets endorsed then
when it gets endorse, it gets changed to 001/03
now i want to select policies only which has "/" this in it, i mean the policies which are endorsed.
select * from policy where txtpolicycode=??how
where do i get documentation of string functions in sql server, i searched the net i couldn't find them

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]

Viewing 15 posts - 1 through 14 (of 14 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

What about WHERE CHARINDEX(...)>0

The documentation of the string functions you get when you look for String Functions in BOL.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 2

well *Sigh*, thats the problem dood, there is no bol installed in my pc.

we dont have sqlserver or client installed, we are having winsql as sqlserver client as it is a free.

pls give me link to the sites , if u have

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

http://msdn.microsoft.com/library/en-us/tsqlref/ts_fa-fz_7oqb.asp

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Btw, BOL is also a free download.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 5

well i did a search using charindex in google.

and i found some use of it.

but that solution was simple it is

using

txtpolicycode like '%/%'

uff.

it was simple i just couldn't think it in the first place

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Congratulations!

Your method effectively ruled out the use of an index and should result in table scans. Not nice on larger tables.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 7

u mean key indexes ?

clustered,non clustered index ?, how come, when u use where clause, role of indexes comes into play is in't it?

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

You should really download BOL from the MS site and work your way through it. It's worth it!

Here are some links on indexes, that also might help you:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;814324

http://support.microsoft.com/default.aspx?scid=kb;EN-US;311826

http://support.microsoft.com/default.aspx?scid=kb;EN-US;325024

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tunesql.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Yoda-193403 SSC Eights! Points: 988 More actions · Answer 9

>>see the policycode=001 .... when it gets endorse, it gets changed to 001/02

no! Two columns you should have. Policy Code is "001", and never changes. Other column, "Endorsements" you shall call it, and a numeric you may make. Start that column with a value of 1. Then, simply increment that column as need.

But what about your pretty format of "001/02", you ask? My data must be this way! no .... a jedi will not confuse formatting of data, or presentation of data, with actual storage. Store it properly, you shall, and present it any way you wish!

select PolicyCode + '/' + right('0' + convert(varchar(2), Endorsements),2) as Your_Nice_Looking_column

from

YourTable

you get the idea ... i hope. Never combine two pieces of information in 1 column! Separation, difficult it is, as you have learned. Combining two columns, much easier !

SQLBill SSC Guru Points: 51440 More actions · Answer 10

Sukhoi,

No one ever said the use of a WHERE clause eliminates the use of the index. Reread what Frank actually said.

Let's consider a basic index, like the one on a dictionary. It indexes by the first letter of each word.

Therefore, when you want to find a word in a dictionary, you look for the index tab with the first letter of that word (ex. to find calibrate in the dictionary, you would turn to the index for C).

But if you don't know the first letter (eg you want to find every word LIKE %lib%), then you need to go through EVERY word in the dictionary to see if there is a match. You can't use the index.

That's the same with SQL Server. Using a WILDCARD as the first part of the search will eliminate the use of the index.

Does that explain it?

-SQLBill

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

To add to Yoda and SQLBill (and without being offending in any way!!!):

You might want to read a book on the fundamentals of relational database design. It will surely save you a LOT of frustrations and long evenings at work. And if I were to suggest one, I would say. "Database design for mere mortals" by Michael Hernandez is a good beginning. Nicely written and easy to understand.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 12

Yo!

we have a seperate tool, which creates tables automatically when we create asp page.

we are limited only to design forms, data tables are created automatically.

we are working on asp and sql server

we design asp pages, using a special tool called converger. when we design the form, a table is automatically created at tht backedn to store the input data.

thats how it works.

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 13

Yo!

we have a seperate tool, which creates tables automatically when we create asp page.

we are limited only to design forms, data tables are created automatically.

we are working on asp and sql server

we design asp pages, using a special tool called converger. when we design the form, a table is automatically created at tht backedn to store the input data.

thats how it works.

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 14

Yo!

we have a seperate tool, which creates tables automatically when we create asp page.

we are limited only to design forms, data tables are created automatically.

we are working on asp and sql server

we design asp pages, using a special tool called converger. when we design the form, a table is automatically created at tht backedn to store the input data.

thats how it works.

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]