Converting data to UPPERCASE

Setup: SQL2000 Server running on Win2k Server
I can't find any difinitive answer to this. I need to convert the data in a
column to uppercase. Do I simply change the collation from the default of
SQL_Latin1_General_Cp1_CI_AS to SQL_Latin1_General_Pref_CP1_CI_AS? And can I
do this while the database is being replicated?
Thanks in advance for your help.
John Steen
No, changing case sensitivity will no change the casing of the data.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:DBF3F536-9B8A-4426-B03D-C1647EDF170B@.microsoft.com...
> Setup: SQL2000 Server running on Win2k Server
> I can't find any difinitive answer to this. I need to convert the data in a
> column to uppercase. Do I simply change the collation from the default of
> SQL_Latin1_General_Cp1_CI_AS to SQL_Latin1_General_Pref_CP1_CI_AS? And can I
> do this while the database is being replicated?
> Thanks in advance for your help.
> John Steen
>
|||Why can't you just do an update?
UPDATE Table
SET col1 = upper(col1)
|||John,
You have to update the column.
update t1
set c1 = upper(c1)
Changing the collation does not change the data.
AMB
"John Steen" wrote:

> Setup: SQL2000 Server running on Win2k Server
> I can't find any difinitive answer to this. I need to convert the data in a
> column to uppercase. Do I simply change the collation from the default of
> SQL_Latin1_General_Cp1_CI_AS to SQL_Latin1_General_Pref_CP1_CI_AS? And can I
> do this while the database is being replicated?
> Thanks in advance for your help.
> John Steen
>
|||Thanks, Alejandro. I was thinking that I not only had to change the
collation, but also reimport the data. This way makes more sense.
But back to the collation -- will changing it to
SQL_Latin1_General_Pref_CP1_CI_AS force new data to be uppercase?
Thanks,
John Steen
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> John,
> You have to update the column.
> update t1
> set c1 = upper(c1)
> Changing the collation does not change the data.
>
> AMB
> "John Steen" wrote:
|||> But back to the collation -- will changing it to
> SQL_Latin1_General_Pref_CP1_CI_AS force new data to be uppercase?
No. Seems you misunderstand what collations are. Where did you learn that any type of collation will
force only uppercase data?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:A4B10FFD-8460-4E79-BFB9-4030E87EF618@.microsoft.com...[vbcol=seagreen]
> Thanks, Alejandro. I was thinking that I not only had to change the
> collation, but also reimport the data. This way makes more sense.
> But back to the collation -- will changing it to
> SQL_Latin1_General_Pref_CP1_CI_AS force new data to be uppercase?
> Thanks,
> John Steen
>
> "Alejandro Mesa" wrote:
|||No that wopn't force anything, you could however do this thru a instead
of trigger or from the front-end
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||I couldn't find a good explanation, so it was an inferrence on my part.
Thanks,
John Steen
"Tibor Karaszi" wrote:

> No. Seems you misunderstand what collations are. Where did you learn that any type of collation will
> force only uppercase data?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
> news:A4B10FFD-8460-4E79-BFB9-4030E87EF618@.microsoft.com...
>
>
|||Thanks, Denis. I'll convert the current data and mention the trigger to our
developer.
John Steen
"SQL" wrote:

> No that wopn't force anything, you could however do this thru a instead
> of trigger or from the front-end
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>

Converting Access SQL to T-Sql

Any help converting the following sql to T-Sql would be helpful. I created it in Access ant works great but cant get the case to work. Need to put it into a accounting program that uses T-Sql. The purpose it to come up with a new field called STATUS based on key words in the "decoded" column.

Thanks!

Status: IIf([TableName]![ColumnName] Like "*PA'D*","PA'D",IIf([TableName]![ ColumnName] Like "*SOLD*","SOLD",IIf([TableName]![ ColumnName] Like "*DNU*","DNU","ACTIVE")))case when TableName.ColumnName Like '%PA''D%'
then 'PA''D'
when TableName.ColumnName Like '%SOLD%'
then 'SOLD'
when TableName.ColumnName Like '%DNU%'
then 'DNU'
else 'ACTIVE' end as Status|||There is a saying that data should be stored in your tables, not in your code. Is there a specific reason you do not have a table for these status codes that you can then reference in your queries? What if one of those status codes changes or a new code needs to be added?sqlsql

Convert Time?

How can I get the current time in 12H format?
I tried convert(nvarchar,GetDate(),108) changing the 108 to other things but
I cant seem to find the right one. Changing Regional Options in control
panel had no affect..
Thnaks
BUCbuc wrote:
> How can I get the current time in 12H format?
> I tried convert(nvarchar,GetDate(),108) changing the 108 to other
> things but I cant seem to find the right one. Changing Regional
> Options in control panel had no affect..
> Thnaks
> BUC
select convert(varchar(20), getdate(), 100)
David Gugick
Imceda Software
www.imceda.com|||109 should do it... mon dd yyyy hh:mi:ss:mmmAM (or PM)
Check Books Online under CONVERT for details.
Paul
"buc" wrote:

> How can I get the current time in 12H format?
> I tried convert(nvarchar,GetDate(),108) changing the 108 to other things b
ut
> I cant seem to find the right one. Changing Regional Options in control
> panel had no affect..
> Thnaks
> BUC
>
>

Convert datetime string. cant find the format i want!!!

i need to be able to convert a datetime to the following format ddmmyyyy but cant seem to find the correct convert code.

looked at this but hasnt got the format i want.
http://databases.aspfaq.com/database/what-are-the-valid-styles-for-converting-datetime-to-string.html

any help is greatly appreciated

Cheers,

Craig

Craig,

You could use something like this:

SELECT replace( convert( varchar(10), getdate(), 104 ), '.', '' )

I hope that you are not storing the data in that format, for it will not be as easy to handle. For storage purposes, use the datetime datatype is best , and even using the ISO form of yyyymmdd and will easily convert back to a datetime datatype. -whereas 'ddmmyyyy' will most likely fail to convert back and require additional steps.

|||no its not for storing it, im actually using it to build another field value 'contactreference' its takes the contacts DOB, Gender & @.@.Identity field. they just want it in a specific format!

Cheers,
Craig|||

Crag:

How are you using the @.@.IDENTITY field? It might be helpful if you provide a little more detail to exactly what you are doing.

|||

its not the actual identity field sorry. its just the max ID + 1

actually one last wee thing

the following would be the required format:-

04072007M00001

for the 00001 part, how would i be able to pad the identity field so that it always had the correct number of zero's in front of it depending on the @.@.identity value?

say identity was 23, i would need it to be 00023 etc

here's the sql so far

SET @.strContactReference = replace(convert( varchar(10), @.dteDateOfBirth, 104 ), '.', '' ) + CAST(@.strGenderRef as nvarchar(1)) + CAST((@.intMaxContactID +1) as nvarchar(4))

Cheers,
Craig

|||

This might work:

Code Snippet

SET @.strContactReference = replace(convert( varchar(10), @.dteDateOfBirth, 104 ), '.', '' ) +
cast(@.strGenderRef AS nvarchar(1)) +
right(( '00000' + ( cast(( @.intMaxContactID + 1 ) AS varchar(5)))), 5 )

Increase the zero padding and increase the right() quantity (the last 5 above) if your IDENTITY values could possiblly exceed 99,999.

|||

104 format reverses the month and the day. Do you want that?

SELECTREPLACE(CONVERT(varchar(10),GETDATE(), 104),'.','') = 07062007

SELECTREPLACE(CONVERT(char(12),GETDATE(), 101),'/','') = 06072007

Adamus

|||

I hope the following sample might help you,

Code Snippet

Create Table #customertroubletickets (

[CustomerName] Varchar(100) ,

[AssignedTicketNo] Varchar(100) Unique

);

Insert Into #customertroubletickets Values('Mani','07062007M00001');

Insert Into #customertroubletickets Values('Arni','07062007M00002');

Insert Into #customertroubletickets Values('Kent','07062007M00003');

Insert Into #customertroubletickets Values('Craig','07062007M00004');

Create Proc #AddTroubleTicket

(

@.CustomerName as varchar(100)

)

as

Begin

Declare @.CurrentDate as varchar(8)

Declare @.MaxNo as varchar(20)

Select @.CurrentDate = Replace(Convert(varchar,Getdate(),103),'/','')

Select @.MaxNo = Max([AssignedTicketNo])

From #customertroubletickets

Where [AssignedTicketNo] Like @.CurrentDate + '%'

Select @.MaxNo = Isnull(@.MaxNo,@.CurrentDate +'M00000')

Select @.MaxNo = @.CurrentDate + 'M' +

Substring(Cast(100000 + Cast(Substring(@.MaxNo,10,5) as Int) + 1 as Varchar),2,5)

Insert Into #customertroubletickets with (TABLOCK) Values (@.CustomerName, @.MaxNo)

End

Exec #AddTroubleTicket 'Adams'

Go

Select * From #customertroubletickets

|||

It's much easier to use the Format function on the front end form before passing it to the the stored procedure if you have that option.

Or else you can concatentate the 5/0's on the beginnnig of the string and then take the right 5.

DECLARE @.mystring varchar(10)

DECLARE @.myprefix varchar(5)

SET @.mystring ='23'

SET @.myprefix ='00000'

SET @.mystring =RIGHT(@.myprefix +''+ @.mystring, 5)

--SELECT @.mystring

SELECTREPLACE(CONVERT(char(12),GETDATE(), 101),'/','')+'M'+ @.mystring

...or if you want the ddmmyyy:

DECLARE @.mystring varchar(10)

DECLARE @.myprefix varchar(5)

SET @.mystring ='23'

SET @.myprefix ='00000'

SET @.mystring =RIGHT(@.myprefix +''+ @.mystring, 5)

--SELECT @.mystring

SELECT REPLACE(CONVERT(varchar(10), GETDATE(), 104), '.', '') + 'M' + @.mystring

Adamus