Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Other SQL Server Topics / April 2007

Tip: Looking for answers? Try searching our database.

sql script

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Brian - 24 Apr 2007 21:49 GMT
How can I change a character in a string of text.  I have a bunch number
that have a lower case l in them and I need to make them an uppercase L.
Example; 99l5555 needs to be 99L5555.
Erland Sommarskog - 24 Apr 2007 22:58 GMT
> How can I change a character in a string of text.  I have a bunch number
> that have a lower case l in them and I need to make them an uppercase L.
> Example; 99l5555 needs to be 99L5555.

UPDATE tbl
SET    col = replace(col, 'l', 'L')

Or if there other lowercase as well that should be uppercase:

UPDATE tbl SET col = upper(col)

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Brian - 25 Apr 2007 15:51 GMT
In your second example, is the word upper referring to a L in this case.
Seems like that would just replace everything in that column with an L?
Sorry, Im kind of a newby and trying to be cautious.

>> How can I change a character in a string of text.  I have a bunch number
>> that have a lower case l in them and I need to make them an uppercase L.
[quoted text clipped - 6 lines]
>
> UPDATE tbl SET col = upper(col)
Erland Sommarskog - 25 Apr 2007 16:33 GMT
> In your second example, is the word upper referring to a L in this case.
> Seems like that would just replace everything in that column with an L?

In the case of:

>> UPDATE tbl SET col = upper(col)

"abc123" will be converted to "ABC123". That is, the upper function replaces
lowercase characters to the corresponding uppercase characters according to
the rules of the collation for the column.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

bubbles - 26 Apr 2007 03:05 GMT
> > How can I change a character in a string of text.  I have a bunch number
> > that have a lower case l in them and I need to make them an uppercase L.
[quoted text clipped - 12 lines]
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I've also been able to replace several characters in a string by
nesting the function.
For example, to replace any ":", "-", " "  to "_", I'll nest the
function like this:

REPLACE(REPLACE(REPLACE(col,':','_'),'-','_'),' ', '_')

Bubbles
Brian - 25 Apr 2007 14:41 GMT
I must be doing something wrong, this is my error.

Msg 547, Level 16, State 0, Line 3
The UPDATE statement conflicted with the REFERENCE constraint
"FK_GL_TrnsLne_IMA". The conflict occurred in database "EAGLE", table
"dbo.GL_TrnsLne", column 'GLL_ItmID'.
The statement has been terminated.

> How can I change a character in a string of text.  I have a bunch number
> that have a lower case l in them and I need to make them an uppercase L.
> Example; 99l5555 needs to be 99L5555.
Erland Sommarskog - 25 Apr 2007 22:44 GMT
> I must be doing something wrong, this is my error.
>
[quoted text clipped - 3 lines]
> "dbo.GL_TrnsLne", column 'GLL_ItmID'.
> The statement has been terminated.

One problem is that we don't know what you are doing. First you ask how
to do something, then you present an error message. That means that we
have to guess.

If you change 99l5555 to 99L5555 and this gives the error above, this
indicates two things:
1) This is a key value, and there are other table referencing that key
  value.
2) The database uses case-sensitive or binary collation.

There are a couple of ways to go. One is to change the constraints
to have ON UPDATE CASCADE.

But it would help to know more about what you are trying to achieve.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

avode - 26 Apr 2007 03:30 GMT
You would use a constraint which forces upper case in the column.
Don't forget to take into account of the column collation.
The next T-SQL script makes the point more clear.

CREATE TABLE #foo_cs(
  col CHAR(7) COLLATE Latin1_General_CS_AI);
GO
CREATE TABLE #foo_ci(
  col CHAR(7) COLLATE Latin1_General_CI_AI);
GO
ALTER TABLE #foo_cs
 ADD CONSTRAINT uppercase_always_cs
     CHECK(col = UPPER(col));
GO
ALTER TABLE #foo_ci
 ADD CONSTRAINT uppercase_always_ci
     CHECK(col COLLATE Latin1_General_CS_AI = UPPER(col));
GO
INSERT INTO #foo_cs(col) VALUES('99l9999');
GO
INSERT INTO #foo_ci(col) VALUES('99l9999');
GO
INSERT INTO #foo_cs(col) VALUES('99L9999');
GO
INSERT INTO #foo_ci(col) VALUES('99L9999');
GO
SELECT col FROM #foo_cs
WHERE col COLLATE Latin1_General_CI_AI = '99l9999';
GO
SELECT col FROM #foo_ci WHERE col = '99l9999';
GO

DROP TAble #foo_cs, #foo_ci;
GO

--
Andrey Odegov
avodeGOV@yandex.ru
(remove GOV to respond)
Brian - 26 Apr 2007 21:15 GMT
Sorry, Let me start over.  I am trying to do a mass change of a bunch of
numbers that a user entered incorrectly.  He entered them with a lower case
letter instead of uppercase.  The numbers all begin with a 99l and I want to
do a mass replace to change them all to a 99L.  The numbers have a suffix
that is not constant like the prefix.  Example; 99l555, 99l556, 99l557 and
so on.

The name of the table is dbo.item and the column is ima_itemid.  Here is my
select statement.

Select * from dbo.item where
 ima_itemid like '99l%'

This result gives me a mixture of 99l's and 99L's.  So apparently the user
eventually started entering them the correct way, in case that is an issue
having a mixture.  It appears that my select statement doesn't care about
case since I get upper and lower, although I used lower in my select.  Hope
this clarifies some.

> How can I change a character in a string of text.  I have a bunch number
> that have a lower case l in them and I need to make them an uppercase L.
> Example; 99l5555 needs to be 99L5555.
Erland Sommarskog - 26 Apr 2007 22:44 GMT
> Sorry, Let me start over.  I am trying to do a mass change of a bunch of
> numbers that a user entered incorrectly.  He entered them with a lower
[quoted text clipped - 14 lines]
> care about case since I get upper and lower, although I used lower in my
> select.  Hope this clarifies some.  

Then this should do it:

  UPDATE item
  SET    ima_itemid = replace(ima_itemid, 'l', 'L')
  WHERE  ima_itemid COLLATE Latin1_General_BIN LIKE '99l%'

By foring a binary collation, only rows with the incorrect pattern
are selected. This should not trigger an FK constraint violation,
given what you have said about both 99l and 99L being returned.

The suggestion from Andrey to add a constraint to prevent this from
happening again is an excellent idea you should pursue.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Brian - 27 Apr 2007 15:28 GMT
>> Sorry, Let me start over.  I am trying to do a mass change of a bunch of
>> numbers that a user entered incorrectly.  He entered them with a lower
[quoted text clipped - 27 lines]
> The suggestion from Andrey to add a constraint to prevent this from
> happening again is an excellent idea you should pursue.

I get this error when I run it.

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the REFERENCE constraint
"FK_GL_TransLine_IMA". The conflict occurred in database "iERP80_EAGLE",
table "dbo.GL_TransLine", column 'GLL_ItemID'.
The statement has been terminated.

Strange thing .... If I change the 99l% to 99L% it updates 5 rows, with the
lowercase l, I get the error.
Erland Sommarskog - 27 Apr 2007 22:43 GMT
> I get this error when I run it.
>
[quoted text clipped - 6 lines]
> Strange thing .... If I change the 99l% to 99L% it updates 5 rows, with
> the lowercase l, I get the error.

So are there are any rows in GL_TransLine with values in the column
GLL_ItemID starting with 99l?

Could you post the CREATE TABLE statements for the two tables, including
the definition of primary and foreign keys?

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.