Ho can I convert first letters of a string to Upper Case (i.e. UNITED KINGDOM - Untited Kingdom). I have country names table which has all entries in uper case. This makes a select box very larg and unproportional.
Thanks in advance for the help.
Php95saj
Currently i have 2 type of data
A and a
But when i try to:
select * from tableA where col = 'a'
then all record with A and a will be selected, any way to avoid it and select only record with col ='a'?
Hi,I am trying to convert string entered in a field to uppercase usingits formula property.I know it can be done using trigger but still I want to use formulaproperty to achieve the same.Any help will be greatly appreciated.-Max
View 3 Replies View RelatedI have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?
View 7 Replies View RelatedIs there a method for converting the first character of a account name to uppercase and the the remaining characters to lower case? I've used the substring procedure but for a name like 'MY NEW COMPANY', how could I convert it to 'My New Company' ?
Thanks,
Terry
Folks,
what script must I use, as a part of CREATE TABLE, to automatically convert characters to UPPER case on insert?
I wrote <CHECK (country = UPPER (country)> in the CREATE TABLE, which was wrong,
because the values were still in the lower case.
The sample script is:
CREATE TABLE address
(street varchar(40),
city varchar(20),
state char (2),
zip varchar (10),
country varchar (20))
When a user types "Canada", I want the inserted value be "CANADA"
Any help is greatly appreciated.
Table :Employee
name Age
'ARUN KUMAR' 30
name column should be displayed
as Arun Kumar
'A' and 'K' need to be upper can i know what function to use??
I am new to SQL and, unfortunately, actually do my work in Access 2003, but I have a question.
This is an example of what the data I am working with looks like:
AALADIN
AA-TACH EH65X, EH65V (V-Twin)
AA-TACH w/Robin 20 & 20.5 h.p. OHV
AA-TACH w/Wisc.-Robin EY21
ABI CONTRACTOR w/Honda 20 h.p. (V-Twin)
The all caps text strings at the beginning of the field need to end up in a separate field than the mixed strings, and the mixed strings need to stay together. The field length varies, as do the lengths of the all caps text strings. There are a lot of records, so I would be interested to know if there was a way to proceed without manually editing each line.
The
Hello, we've an Oracle transition in the pipeline and want to convertall our database objects to upper case. Any one got a script ortechnique (other than manual) to do it?Many thanks, Kevin.
View 2 Replies View RelatedHi,
I have a problem. I need to rename all columns of a database to uppercase. Since SQL SERVER 2005 does not support changing system tables is there a smooth way to do this? Has anyone ideas for a script? point me to the right direction.
I have found the stored procedure sp_rename which could be useful (or would it be better to alter the tables)...
So any help would be appriciated very much...
Regards
fb
Almost all of our character fields are stored in upper-case. Is there an easy way to force SQL Server char and varchar fields to upper-case? Something I can do in SQL Server instead of in the client? It needs to apply to any new records.
There are some exceptions (email addresses for one). I don't mind going through each field and changing something.
Thanks!
hi i want to select * from table1 where name =petter?now if there is many type of petter in table linke PETTER , Petter And petter which record will come in display?if i want all this three (PETTER,Petter,petter) will come in display which command is for this ??? regard
View 4 Replies View RelatedHi,
Can we set the Uper/Lower case in server wide in SQL server 2000 as like case sensitive.
Thanks,
Ravi
While creating a table, can we default a column to be UPPER. So that even if we insert a lower case, it should be converted to UPPER case.
View 4 Replies View RelatedHi
I can't create unique index like that:
1 - create table abc ( colZ varchar(10) )
2 - insert into abc values ( "test")
3 - insert into abc values ("Test")
4 - create unique index idx_abc on abc ( colZ ) -- This doesnt work
.... duplicate key was found for object name abc......
Is not there difference between "Test" and "test"?
Can I work around this?
cheers,
Our sql server 2005 database is receiving data from a third part program over which we have no control. We need to be able to automatically convert data entered in one column of one table to UPPER case only.
How can this be done in the table itself?
An IBM Global Services consultant is telling my client that in order to have SAP BI read any data from any other application supported by SQL Server 2005, that all tables and fields MUST be in UPPER CASE. This would mean that SAP BI could not read ANY data from AdventureWorks (which everyone needs ) but more importantly from 95% of applications written and stored on SQL Server. I find this to be ludicrious, frankly, but don't know how to find out if it is true. Anyone?
View 4 Replies View RelatedHow to parse a string to equal length substrings in SQL
I am getting a long concatenated string from a query (CTVALUE1) and have to use the string in where clause by parsing every 6 characters..
CREATE TABLE [dbo].[PTEMP](
[ID] [char](10) NULL,
[name] [char](10) NULL,
[CTVALUE1] [char](80) NULL
)
INSERT INTO PTEMP
VALUES('11','ABC','0000010T00010L0001000T010C0001')
select * from ptemp
After parsing I have to use these values in a where clause like this
IN('000001','0T0001','0L0001','000T01','0C0001')
Now ,the values can change I mean the string may give 5 values(6 character) today and 10 tomorrow.. So the parsing should be dynamic.
Yeah, it's pretty simple. Maybe it'll help someone out.
-- USAGE: fn_extract_chars(string_to_search, 'letters' -or- 'numbers')
CREATE FUNCTION fn_extract_chars (@x varchar(128), @y char(7))
RETURNS varchar(128)
AS
BEGIN
DECLARE @chars varchar(128)
DECLARE @pos int
DECLARE @action varchar(32)
SET @pos = 0
SET @chars = ''
IF @y = 'numbers' SET @action = '[0-9]'
ELSE IF @y = 'letters' SET @action = '[a-zA-Z]'
WHILE @pos < (DATALENGTH(@x) + 1)
BEGIN
IF PATINDEX(@action,SUBSTRING(@x, @pos, 1)) > 0
BEGIN
SET @chars = @chars + (SELECT SUBSTRING(@x, @pos, 1))
END
SET @pos = @pos + 1
END
RETURN(@chars)
END
Is it possible to pull the index(s) for capital words in a string. For example, if I have a string that says, GetTableName, would it be possible to output, 0, 3, and 8?
View 5 Replies View RelatedI need to build a query, or use a function to removing alfabetic Character from a string.
My string is
WI14000323-0003
The value i want to keep is : 14000323-0003.
I try using this SELECT:
SELECT STUFF(Upper(z.lote), PATINDEX('%[A-Z]%', Upper(z.lote)), 2, '') from mytable
but in this case i have only 2 letters WI, but in the future i dont know if is only 2 letters, 1 letter or more, then is not what i pretend.
Hi There,
I have a column which contain alphanumeric values:
aab123add234cdf423dej553edg543
If I try to return records between these values 'a' and 'e' it will only go as far as d. (first letter)
aab123add234cdf423dej553
This is true if I use where value between a and e
Or if I use greater than equal to operators
Any help would be great.
Thanks
Stuart
I am trying to find all instances of a string that contain the letters FSC. While I am able to find most of them, I am unable to find the ones wrapped in double quotes.
Query example:
Select *
from myTable
Where item like '%FSC%'
This works great with the exception of when FSC is surrounded by double quotes.
ex: MySentencecontains"FSC"
I have tried Replace with no luck.
I am using a select statement likeSELECT ENAME FROM EMPTABLE WHERE ENAME ="vishal"I am getting the result as 1 row affected.Ename----------VishalThe Problem is the query I have passed is "vishal"(lower case) andgetting the output as "Vishal"(V is Upper Case)How do I solve this problem if I want to go with case sensitiveretrival of data.I have gone thru some string functions available but could not findone.Any help would be appreciated..regardsvishal.
View 3 Replies View RelatedFor example,
select fieldA form tableA where fieldA = 'aaa'
I got following output
fieldA
---------
aaa
aAa
AAA
AAa
...
if I want select only the lower case 'aaa', how can I do that?
I am attempting to create a view to bring together multiple joined tables based ona one to many relationship
Eg Table1 contains code, address
Table2 contains code, financial details
table3 contains code, financial history
This view is then going to be used to update a table with only one record for each 'code'. i.e. NewTable = code, add1, add2, city, prov, postal, financialvalue, history value1, history value2
My current stumbling block is:
One of the fields in table1 is a free format address field (address).
eg. could be (street addres, city prov, postal)
or
could be (street address 1, address2, address 3, city prov, postal)
I want to be able to assign individual components of the address to corresponding fields
if # of commas = 2 then
address1 = substring(address,1, position of first comma)
cityprov = substring(address,possition of first comma, position of second comman)
postal = substring(address rest of field)
I have a UDF which returns the number of commas but I cannot figure out how to use either a nested case statement to parse the string...
ANy ideas on how best to accompish this?
( this table is needed for some leacy software which can only handle one record with all infor....
greg
Dear all,
I have building an authitication mecanism through a database standard login table as :
UserName varchar
PassWord varchar
UserId Identity
From my client application I need to validate the username and the password.
For that I have a store procedure with the following statement :
SELECT @loginID = Id from [NomosConfig].[dbo].Users
WHERE [UserName]=@UserName
AND [PassWord] = @pw
From that point this works well except that for the passowrd, whre of course there will be no difference between:
PassWor = myPassw or MYPASSW
How can I check in password field in order that it is case sensitive....?
Later on, the password will be encrypted of course, will it behaves similar ?
thnaks for help
regards
serge
How would I convert an expression like on of these to all upper case first letters with remaining letters lower case? VB has a function for that but sql doesn't seem to. I thought about having a loop go through each character to check for spaces. I've written a couple of similar pieces of code in VB when a while ago, but is there a better way? Thanks :)
Just a couple of typical examples of how the data should appear ~
payment, credit card ==> Payment, Credit Card
butcher & singer ==> Butcher & Singer
Select * FROM TAB1 wher AI_TEXT_VALUE like '%PE%'
The unfortunate thing is i get entries like <Perennial allergic rhinitis>
I am looking specifically for the term PE ( All upper case ) But keep in mind, I would need to use the LIKE operator in my where clause.
( just like in the example above )
I have a scenario which i must check if the value of the inputted word is exact (case sensitive) in a string. And print the word if correct. using a user defined function.
Example:The quick brown fox jump over the lazy dog.
If the user enter "jump" it must print "jump".
If the user enter "Jump" it must print "jump" which is the correct word.
I am required to find or check that a specific word exists in string or not.
Suppose I have to find the word 'st' than I need the result true if and only if the following occurrences are there.
1. 'St is valid;' -> true
2. 'DOB is valid;ST is invalid;' -> true
3. 'DOB is valid; ST is invalid;' -> true
4. 'DOB is valid;invalid ST;' -> true
5. 'DOB is valid; invalid ST;' -> true
6. 'DOB is valid; invalid STate;' -> false
Means the exact ST should be search. It is not free text search.
T-SQL is needed to be used in select statement with case using PATINDEX, RegEx or any suitable t-sql command.
how SQL 2012 would treat a literal string for a comparison similar to below. I want to ensure that the server isn't implicitly converting the value as it runs the SQL, so I'd rather change the data type in one of my tables, as unicode isn't required.
Declare @T Table (S varchar(2))
Declare @S nvarchar(255)
Insert into @T
Values ('AR'), ('AT'), ('AW')
Set @S = 'Auto Repairs'
Select *
from @T T
where case @S when 'Auto Repairs' then 'AR'
when 'Auto Target' then 'AT'
when 'Auto Wash' then 'AW' end = T.STo summarise
in the above would AR, AT and AW in the case statement be treated as a nvarchar, as that's the field the case is wrapped around, or would it be treated as a varchar, as that's what I'm comparing it to.
Hi All,
I have SQL query as:
SELECT CASE @sortcolumn
WHEN 'TYPE' THEN TYPE_TXT
WHEN 'DATE' THEN CHECKOUT_TS
WHEN 'ACCT' THEN ACCT_NBR
WHEN 'FIRST' THEN FIRST_NM
WHEN 'LAST' THEN LAST_NM
WHEN 'DAYS' THEN DATEDIFF(dd, CHECKOUT_TS, GETDATE())
ELSE DELIVER_ID END
FROM REQUEST
If @sortcolumn has 'DATE' or 'DAYS' it runs fine....any other value, I get an exception "Conversion failed when converting datetime from character string". All columns except CHECKOUT_TS are of type varchar.
Any help would be greatly appreciated.
Thanks,
Shashi