Database

Re: Interesting question about a MIN function and usage

29-Dec-09 0:57

James Shao wrote:
which doesn't work

James Shao wrote:
got an error internet message

that is too vague, nobody can help you without a more accurate description.

Hmmm | :|

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

Merry Christmas and a Happy New Year to all.

James Shao29-Dec-09 4:24

James Shao

29-Dec-09 4:24

Thanks for the reply Luc. Here's a more accurate discription of what my DataTable looks like:

Ticker Date Price
MSFT 1/2/2009 20
MSFT 1/3/2009 19
MSFT 1/4/2009 18
MSFT 1/5/2009 17
MSFT 1/6/2009 16
MSFT 1/7/2009 18
MSFT 1/8/2009 20
MFST 1/9/2009 21
DELL 1/2/2009 11
DELL 1/3/2009 12
DELL 1/4/2009 17
DELL 1/5/2009 17
DELL 1/6/2009 16
DELL 1/7/2009 15
DELL 1/8/2009 15
DELL 1/9/2009 14
AAPL
...
...
...

I need a query to return all the stocks with its price first going down then going up, in other words a V pattern; and the query would need to check whether this happens for a particular date. In the example above, this happened to MSFT on 1/7/2009 when the first V patter is formed.

Not sure if this can be done using SQL, but if possible, could you please help me with it? I'd be very very thankful! Smile | :)

Re: Interesting question about a MIN function and usage [modified]

Corporal Agarn29-Dec-09 4:30

Corporal Agarn

29-Dec-09 4:30

<br />
select @minval = min(stockval) from table where stockdate between d1 and d2<br />
select @mindate = stockdate from table where stockdate between d1 and d2 and stockval = @minval<br />

This is just a starting point.

Good luck

modified on Wednesday, December 30, 2009 8:13 AM

Re: Interesting question about a MIN function and usage

James Shao30-Dec-09 12:47

James Shao

30-Dec-09 12:47

Thank you djj55, a question about the query statement you wrote. Is that 2 different SQL statement, as I don't see anything between the first SELECT and the second SELECT statement. Or could you actually write a statement like that? Because I thought the @minval in the second statement is not defined....of course it's defined in the first statement, but the two statements are not connected, right?

Re: Interesting question about a MIN function and usage

Corporal Agarn31-Dec-09 1:05

Corporal Agarn

31-Dec-09 1:05

Sorry for the confusion. I did notice that I did a cut & paste instead of a copy & paste so I edited my query.

There should be two queries

select @minval = min(stockval) from table where stockdate between d1 and d2;
select @mindate = stockdate from table where stockdate between d1 and d2 and stockval = @minval;

Not being a C programmer I do not normally use the semicolon which in T-SQL is not needed.

Hope this helps.

Problem with the french character with sql server 2005

pranavcool27-Dec-09 18:44

pranavcool

27-Dec-09 18:44

Hi,

I am facing some problems related to french characters. One of the character "₣" is not supported by sql server 2005. rest of the french characters is suppoeted by sql server 2005.

When ever i tried to insert data or run a select query it returns as a question mark "?".

Full text search is provided on table for search functionality.

Select doc_id,1 from DocumentMaster Where 1=1  
AND  (CONTAINS(Document_Filename,  '"*Œ*"') OR  CONTAINS(Document_Filename_French,  '"*Œ*"'))  
AND  (CONTAINS(Document_Filename,  '"*œ*"') OR  CONTAINS(Document_Filename_French,  '"*œ*"'))  
AND  (CONTAINS(Document_Filename,  '"*€*"') OR  CONTAINS(Document_Filename_French,  '"*€*"'))  
AND  (CONTAINS(Document_Filename,  '"*?*"') OR  CONTAINS(Document_Filename_French,  '"*?*"'))

i have read in couple of the site that the data type of the colum should be nvarchar or ntext for french characters to support. I even that. but then also it is not supporting the "₣" french character. Rest french character it is supporting.

please suggest any solution.

Regards,

Pranav Dave

Re: Problem with the french character with sql server 2005

Luc Pattyn28-Dec-09 1:33

Re: Problem with the french character with sql server 2005

28-Dec-09 1:33

Hi,

I don;t know the F-like character your message shows, and which you call a "french character".

What is its name?
Can you show us some words containing it?
do you know its unicode number?
are there things you can do with it successfully?

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

Merry Christmas and a Happy New Year to all.

Eddy Vluggen28-Dec-09 2:24

Re: Problem with the french character with sql server 2005

28-Dec-09 2:24

Luc Pattyn wrote:
What is its name?

"French Franc", a deprecated monetary unit Smile | :)

Luc Pattyn wrote:
are there things you can do with it successfully?

Compare beer-prices with aged Frenchmen?

I are Troll Suspicious | :suss:

Luc Pattyn28-Dec-09 2:33

Re: Problem with the french character with sql server 2005

28-Dec-09 2:33

Eddy Vluggen wrote:
"French Franc"

really? doesn't look anything like the symbol here[^], which is Unicode U+20A3 and should pose no problem at all.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

Merry Christmas and a Happy New Year to all.

Eddy Vluggen28-Dec-09 2:52

Re: Problem with the french character with sql server 2005

28-Dec-09 2:52

Luc Pattyn wrote:
really?

Yup, they're the same Smile | :)

Well, ehr.. the example doesn't look like that picture, but like the font that it shows below. Scroll down on that link that you sent me and the same symbol should be there in the table "Java Data". It might be that your browser is (temporarily?) set to something different than UTF-8? Or you're using a different font-set, that could also explain it's different appearance.

It shouldn't pose a problem like you said. Then again, it doesn't behave like the 'other' special characters. If I omit the N-prefix in the INSERT statement, then it will indeed put a questionmark in there. Not so for that crazy 'Œ' symbol.

I are Troll Suspicious | :suss:

Luc Pattyn28-Dec-09 3:09

Re: Problem with the french character with sql server 2005

28-Dec-09 3:09

yup.

the 'Œ' character (and eszet 'ß' and most accented characters used in Western languages) is part of code page 1252, whereas the French Franc and most other (possibly obsolete) monetary symbols are not.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

Merry Christmas and a Happy New Year to all.

Eddy Vluggen28-Dec-09 6:34

Re: Problem with the french character with sql server 2005 [modified]

28-Dec-09 6:34

Luc Pattyn wrote:
the 'Œ' character (and eszet 'ß' and most accented characters used in Western languages) is part of code page 1252, whereas the French Franc and most other (possibly obsolete) monetary symbols are not.

Not even the need for a CHCP 1252, since it gets stuffed into a unicode field.

Question seems answered though, I'll wait for the repost Big Grin | :-D

I are Troll Suspicious | :suss:

Eddy Vluggen28-Dec-09 2:19

Help me understand this unusual query... [modified]

28-Dec-09 2:19

Try this one;

DECLARE @TestTable AS TABLE (
    TestCol NVARCHAR(2) COLLATE Latin1_General_BIN
)
INSERT INTO @TestTable VALUES (N'₣')
INSERT INTO @TestTable VALUES (N'Œ')
SELECT * FROM @TestTable

Check whether the column that you're storing data into is really accepting unicode-text, make sure the collation is set correctly, and check whether you're passing unicode or ascii as a parameter Smile | :)

--edit--

pranavcool wrote:
i have read in couple of the site that the data type of the colum should be nvarchar or ntext for french characters to support.

That is correct. Unfortunatly, there's other places where things can go wrong. Even the value of the string that you're inspecting in the debugger is displayed using a font. The task would be to verify that SQL Server works correctly, and then to find the source of the error.

pranavcool wrote:
When ever i tried to insert data or run a select query it returns as a question mark "?".

I'm guessing that this is due to the Management Studio, since you're probably not on DOS using isql. Try a small console-application, you'll see that SQL Server correctly saves and retrieves the character. That is, using the standard SqlCommand and SqlConnection.

You're not displaying the result/seeing the '?' in a browser by any chance?

Are you using MONO? Or an ODBC-driver?

I are Troll Suspicious | :suss:

modified on Monday, December 28, 2009 12:34 PM

Rafferty Uy27-Dec-09 15:44

27-Dec-09 15:44

Hi,

There was a small problem with our app where it allowed users to enter white-space only characters in one of the data fields. So now I'm trying to clean this up.
A colleague of mine sent me this unusual SQL query to find the whitespaces. The script looks like this:

SELECT tblId, ASCII(tblField1) AS 'ASCII Code', tblField1
FROM Table1
WHERE ASCII(LEFT(tblField1,1)) = 32

This query returned 40 rows.

Now, when I try either of the following:

SELECT tblId, ASCII(tblField1) AS 'ASCII Code', tblField1
FROM Table1
WHERE tblField1 = ' ' -- with a single whitespace

SELECT tblId, ASCII(tblField1) AS 'ASCII Code', tblField1
FROM Table1
WHERE tblField1 = '' -- empty string

The above 2 queries return 200,000+ rows.

Why is this so?

Rafferty

modified on Sunday, December 27, 2009 10:23 PM

Luc Pattyn28-Dec-09 1:46

28-Dec-09 1:46

Hi,

the first query selects all rows where tblField1 holds a string with a first character equal to a space.

the other two require the entire tblField1 to equal something that probably gets trimmed down to an empty string, hence returning all rows with that field empty. That obviously is quite a different query.

IMO the more elegant query would use

...WHERE tblField1 LIKE ' %'

using a single space and the wildcard character %.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

Merry Christmas and a Happy New Year to all.

Rafferty Uy28-Dec-09 15:36

28-Dec-09 15:36

So the = operator does not check for exact string equality... I knew before that = is case insensitive but to think that it also trims the string!

Is LIKE the more preferable conditional operator then?
Is there a performance difference between = and LIKE?

Say for example WHERE tblField1 = 'something' vs. WHERE tblField1 LIKE 'something'

Rafferty

Luc Pattyn28-Dec-09 15:41

28-Dec-09 15:41

1. I'm not sure, I think it stripped the spaces, got an empty constant, and returned all records.

2.
LIKE makes most sense when it contains a wildcard, and = does not accept wildcards; without wildcards databases can take big advantage of indexing, with wildcards they can't, so most often LIKE is much slower.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

Merry Christmas and a Happy New Year to all.

Rafferty Uy28-Dec-09 15:46

28-Dec-09 15:46

So using LIKE will not use the database indexing even if there are no wildcards in the passed string?

Rafferty

Luc Pattyn28-Dec-09 15:55

28-Dec-09 15:55

I don't know what goes on inside a specific database, that is up to the guys implementing all of it; it may or may not be documented, and it might depend on circumstances, such as available memory and disk space. The simple rule is: use LIKE if you need a wildcard, and expect it to be slower.

If you need more, look for the documentation and perform your own experiments (on sufficiently large data sets).

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

Merry Christmas and a Happy New Year to all.

Rafferty Uy28-Dec-09 16:00

28-Dec-09 16:00

hm... maybe I'll do some experimenting when I find the time.

Thanks for your help!

Rafferty

Luc Pattyn28-Dec-09 16:02

28-Dec-09 16:02

You're welcome.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

Merry Christmas and a Happy New Year to all.

WoutL28-Dec-09 21:24

WoutL

28-Dec-09 21:24

Microsoft SQLServer will use indexes (if available) when you use the like statement without wildcards. Even if you do use wildcards, SQL will use indexes if possible. if you compare to a string witch starts with a wildcard, SQL will NOT use indexes.

Wout Louwers

Rafferty Uy29-Dec-09 15:34

29-Dec-09 15:34

Wow this is very useful!
Thanks a lot! Thumbs Up | :thumbsup:

Rafferty