converting lowercase to uppercase

Question

converting lowercase to uppercase

smsam1

SSC-Addicted

Points: 497
More actions
June 20, 2009 at 10:18 am

#207182

Create table Patient (PatientId int,
FullName varchar(30),
flag char(1)
constraint ss check (adp = 't' or adp = 'f')
)
i want to store only T or F in the flag field . for example if i enter 't' it should be stored as 'T' and vice versa.
any help would be very usefull.

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

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

1. you can put this conversion on the front end application that will convert the field to uppercase...

2. i dont know if this the best practice but you can write a insert/udpate trigger that causes the insert/update of this column in upper case.

Pradeep Singh

Lowell SSC Guru Points: 323518 More actions · Answer 2

pradeep's suggestion for a trigger to uppercase the input would work, and you could force only the upper case values to be inserted with a slightly different constraint:

Create table Patient (PatientId int,

FullName varchar(30),

flag char(1)

constraint ss check (ascii(adp) = 84 or ascii(adp) = 70) --'T' or 'F',116 or 102 for 't' or 'f'

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

If you are using a stored procedure to input the data you could use:'INSERT INTO dbo.Patient(Patientid,fullname,flag)

VALUES(1002,'my full name is',UPPER('f'))

If using a stored procedure to extract the data and pass it to your interface application use:

SELECT patientid, fullname,UPPER(flag) FROM dbo.patient

Or use either Lowell's or ps's suggestions - either way hope this assists you

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

smsam1 (6/20/2009)
i want to store only T or F in the flag field . for example if i enter 't' it should be stored as 'T' and vice versa.

Why? Is your server case sensitive? If not, it will not matter in any of your code except that forcing it to change will only take extra time and resources.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

smsam1 SSC-Addicted Points: 497 More actions · Answer 5

since i want only to show 't' or 'f' in upper case letters, i will use

select upper(flag)

from patient

thank you for everyone who helped me!

ps. One Orange Chip Points: 29422 More actions · Answer 6

smsam1 (6/21/2009)
since i want only to show 't' or 'f' in upper case letters, i will use
select upper(flag)
from patient
thank you for everyone who helped me!

You first post said u want to store caps letter in that field. It seems now u're okay with storing lower caps and then showing it as upper caps.

Anyways glad you got it working as per your requirement:-).

Pradeep Singh

smsam1 SSC-Addicted Points: 497 More actions · Answer 7

oh dear don think abt it

'cause user requirements change very rapidly

sqlguru-1118922 Old Hand Points: 339 More actions · Answer 8

The database is not suppose to format data on the fly, that's not it's job. RDMS are very weak at computational stuff like formatting. This should be handled in the middle-tier/front-end.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

sqlguru (6/24/2009)
The database is not suppose to format data on the fly, that's not it's job. RDMS are very weak at computational stuff like formatting. This should be handled in the middle-tier/front-end.

Heh... I halfway agree... you can do some awesome highspeed formatting in SQL Server and, in the absence of a GUI or reporting program, such formatting is perfect for batch formatting of information destined to become a file. Other than that, I agree. Formatting should be done on the GUI side but not because of any short coming on the SQL Server side... it's because you want the local settings to be able to do the conversion on the GUI side. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 10

Or use COLLATION

CONSTRAINT ss CHECK (adp IN ('T', 'F') COLLATE SQL_Latin1_General_Cp437_BIN)

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

Peso (6/29/2009)
Or use COLLATION
CONSTRAINT ss CHECK (adp IN ('T', 'F') COLLATE SQL_Latin1_General_Cp437_BIN)

That's a good check but I'd have to introduce someone to a serious volley of high speed pork chops for making a true/false answer case sensitive. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 12

Maybe T means "Absolutely 100% true", and t means "well, maybe about 75% true, give or take..." ?

🙂

N 56°04'39.16"
E 12°55'05.25"

smsam1 SSC-Addicted Points: 497 More actions · Answer 13

smsam1

SSC-Addicted

Points: 497

June 29, 2009 at 11:43 pm

#1017382

thaks all for helping me...

AmolNaik SSCarpal Tunnel Points: 4767 More actions · Answer 14

Best is to leave the formatting and case conversions at the front end application, should try not to use SQL Server for formatting and case conversion tasks which are easily handled at the application side.

Rememer RDBMS are designed for data storage and data retrieval as quick as possible.

Thanks,

Amol

Amol Naik