query

Hi
I have a following problem;
Lets say i have table with names and value in this table "hkan"
Now user enters text "hakan" for search and query must return alsow
"hkan".
Is in SQL any "special" function for this ?
one option is create own function, but there is toooo many different options
a-, o- aso aso.
Regards;
Meelis
Meelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI =
@.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "hkan"
> Now user enters text "hakan" for search and query must return alsow
> "hkan".
> Is in SQL any "special" function for this ?
> one option is create own function, but there is toooo many different
> options a-, o- aso aso.
>
> Regards;
> Meelis
>

query

Hi
I have a following problem;
Lets say i have table with names and value in this table "håkan"
Now user enters text "hakan" for search and query must return alsow
"håkan".
Is in SQL any "special" function for this :)?
one option is create own function, but there is toooo many different options
a-ä, o-ö aso aso.
Regards;
MeelisMeelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI = @.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "håkan"
> Now user enters text "hakan" for search and query must return alsow
> "håkan".
> Is in SQL any "special" function for this :)?
> one option is create own function, but there is toooo many different
> options a-ä, o-ö aso aso.
>
> Regards;
> Meelis
>

query

Hi
I have a following problem;
Lets say i have table with names and value in this table "hkan"
Now user enters text "hakan" for search and query must return alsow
"hkan".
Is in SQL any "special" function for this ?
one option is create own function, but there is toooo many different options
a-, o- aso aso.
Regards;
MeelisMeelis,
This is where collations can help you. (Or hurt you.) If you were running a
Case Insensitive, Accent Insensitive collation it would do exactly what you
want. But do you always want this? Or only some of the time?
If you want to change the column definition:
ALTER TABLE MyTable
ALTER COLUMN MyAccentedColumn NVARCHAR(128)
COLLATE SQL_Latin1_General_Cp1_CI_AI
If you want to handle it in a query:
SELECT MyAccentedColumn
FROM MyTable
WHERE MyAccentedColumn COLLATE SQL_Latin1_General_Cp1_CI_AI =
@.MyParm COLLATE SQL_Latin1_General_Cp1_CI_AI
Of course, you should read about how collations work before jumping into
this, so that you choose the behaviour that you desire.
RLF
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:%23M0lfLtaHHA.4948@.TK2MSFTNGP05.phx.gbl...
> Hi
> I have a following problem;
> Lets say i have table with names and value in this table "hkan"
> Now user enters text "hakan" for search and query must return alsow
> "hkan".
> Is in SQL any "special" function for this ?
> one option is create own function, but there is toooo many different
> options a-, o- aso aso.
>
> Regards;
> Meelis
>

Quarterly trending

I have a reporting query that I need to do a quarterly trending on. A
user enters a begining date and end date, and from there, I have to
determine the closest quarter to it.

Also, the actual Quarter starts on Sept 4th, then add 13 weeks to that,
will be the second quarter.

I was able to do the trending for week/month/year, but I'm stuck on
this quarterly trending.

Any help would be greatly appreciated.

Example of the monthly trending that I have written:

SELECT [VALUE],DATEPART(MONTH,DATESAMPLE) AS[MONTH]
FROM #TABLE
GROUP BY [VALUE], DATEPART(MONTH,DATESAMPLE)
ORDER BY EDIT_SP, DATEPART(MONTH,DATESAMPLE)This would be easy if your business used calendar quarters instead;
can't you make them change? <G> You could then group by
DATEPART(Quarter, DateSample)

Since that's not likely, a solution might be to use a calendar table
that takes a key Date and maps it to the appropriate quarter, eg:

CREATE TABLE Calendar (DateKey smalldatetime, PeriodQuarter int...
(other columns))

INSERT INTO Calendar (DateKey, PeriodQuarter)
SELECT '9/1/2005', 2
UNION ALL
SELECT '9/4/2005', 3

etc

You could then join and group on the PeriodQuarter value.

Just an idea.
Stu|||create a table of the quarters:

CREATE TABLE Quarters
(quarter_name CHAR(20) NOT NULL PRIMARY KEY,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date ));

>> .. determine the closest quarter to it. <<

Whatr does thst mean? give me an algorithm.