473,326 Members | 2,061 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

sql script

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.
Apr 24 '07 #1
11 4405
Brian (b.********@eaglecrusher.com) writes:
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)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 24 '07 #2
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.

"Brian" <b.********@eaglecrusher.comwrote in message
news:46***********************@roadrunner.com...
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.

Apr 25 '07 #3
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.

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Brian (b.********@eaglecrusher.com) writes:
>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)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Apr 25 '07 #4
Brian (b.********@eaglecrusher.com) writes:
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.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 25 '07 #5
Brian (b.********@eaglecrusher.com) writes:
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.
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.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 25 '07 #6
On Apr 25, 5:58 am, Erland Sommarskog <esq...@sommarskog.sewrote:
Brian (b.hough...@eaglecrusher.com) writes:
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)

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

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

Apr 26 '07 #7
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
av******@yandex.ru
(remove GOV to respond)

Apr 26 '07 #8
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.
"Brian" <b.********@eaglecrusher.comwrote in message
news:46***********************@roadrunner.com...
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.

Apr 26 '07 #9
Brian (b.********@eaglecrusher.com) writes:
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.
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.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 26 '07 #10
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Brian (b.********@eaglecrusher.com) writes:
>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.

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.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
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.
Apr 27 '07 #11
Brian (b.********@eaglecrusher.com) writes:
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.
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?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 27 '07 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Mike Daniel | last post by:
I am attempting to use document.write(pageVar) that displays a new html page within a pop-up window and the popup is failing. Also note that pageVar is a complete HTML page containing other java...
1
by: bayouprophet | last post by:
Cant get menu script to to put linked page in the same frame. I am new to Java and I am wondering what am I doing wrong? below are my java applet file, frame.html file, and my text file and one...
1
by: Allen | last post by:
I am trying to add an additional photo/hyperlink to the company web site (I didn't create it) without any luck. The mouseover feature 'highlights' pics by swapping them with another pic using this...
3
by: Water Cooler v2 | last post by:
Questions: 1. Can there be more than a single script block in a given HEAD tag? 2. Can there be more than a single script block in a given BODY tag? To test, I tried the following code. None...
2
by: bilaribilari | last post by:
Hi all, I am using Tidy (C) for parsing html pages. I encountered a page that has some script as follows: <script> .... var abc = "<script>some stuff here</" + "script>"; .... </script>
19
by: thisis | last post by:
Hi All, i have this.asp page: <script type="text/vbscript"> Function myFunc(val1ok, val2ok) ' do something ok myFunc = " return something ok" End Function </script>
3
by: rsteph | last post by:
I have a script that shows the time and date. It's been working on my site for quite a while now. Suddenly it stops showing up, after getting my drop down menu to work. If I put text between the...
3
by: Angus | last post by:
I have a web page with a toolbar containing a Save button. The Save button can change contextually to be a Search button in some cases. Hence the button name searchsavechanges. The snippet of...
7
by: imtmub | last post by:
I have a page, Head tag Contains many Scripts and style sheet for Menu and Page. This code working fine and displaying menus and page as i wanted. Check this page for reference....
1
KevinADC
by: KevinADC | last post by:
Note: You may skip to the end of the article if all you want is the perl code. Introduction Many websites have a form or a link you can use to download a file. You click a form button or click...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.