|
 |
asp_databases thread: replace
Message #1 by j_halstad@s... on Tue, 18 Feb 2003 11:24:01
|
|
I need to place the letters SR in front of any numbers where those letters
are missing in a table. Am using SQL DB and the field is varchar. I have
looked at regular expressions but don't feel confident using them. I might
muck everything up.
Message #2 by "Pat Wong" <vinyl-junkie@n...> on Tue, 18 Feb 2003 04:25:21 -0800
|
|
I'm a fairly new to this but I'm gonna give it a try. You don't say what you want to replace
the letters SR with, but here's the basic format of the Replace method.
Replace(varchar,SR,whatever you want to replace SR with)
Hope this helps.
----- Original Message -----
From: <j_halstad@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, February 18, 2003 11:24 AM
Subject: [asp_databases] replace
| I need to place the letters SR in front of any numbers where those letters
| are missing in a table. Am using SQL DB and the field is varchar. I have
| looked at regular expressions but don't feel confident using them. I might
| muck everything up.
~8^) Pat Wong (ICQ #61070813)
http://www.napathon.com/
-------------------------------------------------------------------
Senility Prayer:
God grant me the senility to forget the people I never
liked anyway, the good fortune to run into the ones I
like, and the eyesight to tell the difference.
-------------------------------------------------------------------
Message #3 by Colin.Montgomery@C... on Tue, 18 Feb 2003 12:53:11 -0000
|
|
i'm not sure what you want - do you have a field with some records which are
in the format "SR123" while others are in the format "123" ? And you want
to update the "123" ones to read "SR123" ??
If so the following SQL should do it (works in Access and I don't see any
reason for it not to work in SQL Server -apart from needing % rather than
*):
UPDATE Table1
SET Table1.myStr = "SR" & Table1.myStr
WHERE Table1.myStr Not Like "SR*"
You might need to use some ANDs or ORs in the where clause to get exactly
what you want.
If this doesn't help try to let us know what it is you want to achieve -
maybe include some sample data.
Col
-----Original Message-----
From: Pat Wong [mailto:vinyl-junkie@n...]
Sent: 18 February 2003 12:25
To: ASP Databases
Subject: [asp_databases] Re: replace
I'm a fairly new to this but I'm gonna give it a try. You don't say what you
want to replace
the letters SR with, but here's the basic format of the Replace method.
Replace(varchar,SR,whatever you want to replace SR with)
Hope this helps.
----- Original Message -----
From: <j_halstad@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, February 18, 2003 11:24 AM
Subject: [asp_databases] replace
| I need to place the letters SR in front of any numbers where those letters
| are missing in a table. Am using SQL DB and the field is varchar. I have
| looked at regular expressions but don't feel confident using them. I might
| muck everything up.
~8^) Pat Wong (ICQ #61070813)
http://www.napathon.com/
-------------------------------------------------------------------
Senility Prayer:
God grant me the senility to forget the people I never
liked anyway, the good fortune to run into the ones I
like, and the eyesight to tell the difference.
-------------------------------------------------------------------
*******
This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the
intended recipient, please telephone or email the sender and delete this message and any attachment from your system. If you are
not the intended recipient you must not copy this message or attachment or disclose the contents to any other person.
For further information about Clifford Chance please see our website at http://www.cliffordchance.com or refer to any Clifford
Chance office.
Message #4 by Jean Halstad <J_Halstad@S...> on Tue, 18 Feb 2003 13:18:26 -0000
|
|
Yes, that's it.
-----Original Message-----
From: Colin.Montgomery@C...
[mailto:Colin.Montgomery@C...]
Sent: 18 February 2003 12:53
To: ASP Databases
Subject: [asp_databases] Re: replace
i'm not sure what you want - do you have a field with some records which are
in the format "SR123" while others are in the format "123" ? And you want
to update the "123" ones to read "SR123" ??
If so the following SQL should do it (works in Access and I don't see any
reason for it not to work in SQL Server -apart from needing % rather than
*):
UPDATE Table1
SET Table1.myStr = "SR" & Table1.myStr
WHERE Table1.myStr Not Like "SR*"
You might need to use some ANDs or ORs in the where clause to get exactly
what you want.
If this doesn't help try to let us know what it is you want to achieve -
maybe include some sample data.
Col
-----Original Message-----
From: Pat Wong [mailto:vinyl-junkie@n...]
Sent: 18 February 2003 12:25
To: ASP Databases
Subject: [asp_databases] Re: replace
I'm a fairly new to this but I'm gonna give it a try. You don't say what you
want to replace
the letters SR with, but here's the basic format of the Replace method.
Replace(varchar,SR,whatever you want to replace SR with)
Hope this helps.
----- Original Message -----
From: <j_halstad@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, February 18, 2003 11:24 AM
Subject: [asp_databases] replace
| I need to place the letters SR in front of any numbers where those letters
| are missing in a table. Am using SQL DB and the field is varchar. I have
| looked at regular expressions but don't feel confident using them. I might
| muck everything up.
~8^) Pat Wong (ICQ #61070813)
http://www.napathon.com/
-------------------------------------------------------------------
Senility Prayer:
God grant me the senility to forget the people I never
liked anyway, the good fortune to run into the ones I
like, and the eyesight to tell the difference.
-------------------------------------------------------------------
*******
This message and any attachment are confidential and may be privileged or
otherwise protected from disclosure. If you are not the intended recipient,
please telephone or email the sender and delete this message and any
attachment from your system. If you are not the intended recipient you must
not copy this message or attachment or disclose the contents to any other
person.
For further information about Clifford Chance please see our website at
http://www.cliffordchance.com or refer to any Clifford Chance office.
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed
If you have received this e-mail in error please notify
seafish@s...
If the content is not about the business of the Sea Fish Industry Authority
or the sea fish industry then the message is neither from nor sanctioned
by the Sea Fish Industry Authority.
Working with the Seafood industry to satisfy consumers,
raise standards, improve efficiency and secure a sustainable future.
Web Site: www.seafish.co.uk
Message #5 by Jean Halstad <J_Halstad@S...> on Tue, 18 Feb 2003 13:47:18 -0000
|
|
This is the query I wrote
update reports
set reports.reportNo = "SR" & reports.reportNo
where reports.reportNo not like "SR"
This is the error message I got
Msg 403, Level 16, State 1
Invalid operator for datatype op: boolean AND type: char
-----Original Message-----
From: Colin.Montgomery@C...
[mailto:Colin.Montgomery@C...]
Sent: 18 February 2003 12:53
To: ASP Databases
Subject: [asp_databases] Re: replace
i'm not sure what you want - do you have a field with some records which are
in the format "SR123" while others are in the format "123" ? And you want
to update the "123" ones to read "SR123" ??
If so the following SQL should do it (works in Access and I don't see any
reason for it not to work in SQL Server -apart from needing % rather than
*):
UPDATE Table1
SET Table1.myStr = "SR" & Table1.myStr
WHERE Table1.myStr Not Like "SR*"
You might need to use some ANDs or ORs in the where clause to get exactly
what you want.
If this doesn't help try to let us know what it is you want to achieve -
maybe include some sample data.
Col
-----Original Message-----
From: Pat Wong [mailto:vinyl-junkie@n...]
Sent: 18 February 2003 12:25
To: ASP Databases
Subject: [asp_databases] Re: replace
I'm a fairly new to this but I'm gonna give it a try. You don't say what you
want to replace
the letters SR with, but here's the basic format of the Replace method.
Replace(varchar,SR,whatever you want to replace SR with)
Hope this helps.
----- Original Message -----
From: <j_halstad@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, February 18, 2003 11:24 AM
Subject: [asp_databases] replace
| I need to place the letters SR in front of any numbers where those letters
| are missing in a table. Am using SQL DB and the field is varchar. I have
| looked at regular expressions but don't feel confident using them. I might
| muck everything up.
~8^) Pat Wong (ICQ #61070813)
http://www.napathon.com/
-------------------------------------------------------------------
Senility Prayer:
God grant me the senility to forget the people I never
liked anyway, the good fortune to run into the ones I
like, and the eyesight to tell the difference.
-------------------------------------------------------------------
*******
This message and any attachment are confidential and may be privileged or
otherwise protected from disclosure. If you are not the intended recipient,
please telephone or email the sender and delete this message and any
attachment from your system. If you are not the intended recipient you must
not copy this message or attachment or disclose the contents to any other
person.
For further information about Clifford Chance please see our website at
http://www.cliffordchance.com or refer to any Clifford Chance office.
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed
If you have received this e-mail in error please notify
seafish@s...
If the content is not about the business of the Sea Fish Industry Authority
or the sea fish industry then the message is neither from nor sanctioned
by the Sea Fish Industry Authority.
Working with the Seafood industry to satisfy consumers,
raise standards, improve efficiency and secure a sustainable future.
Web Site: www.seafish.co.uk
Message #6 by imran.saleem@b... on Tue, 18 Feb 2003 13:52:54 -0000
|
|
update reports
set reports.reportNo = "SR" & reports.reportNo
where reports.reportNo not like 'SR%'
-----Original Message-----
From: Jean Halstad [mailto:J_Halstad@S...]
Sent: Tuesday, February 18, 2003 1:47 PM
To: ASP Databases
Subject: [asp_databases] Re: replace
This is the query I wrote
update reports
set reports.reportNo = "SR" & reports.reportNo
where reports.reportNo not like "SR"
This is the error message I got
Msg 403, Level 16, State 1
Invalid operator for datatype op: boolean AND type: char
-----Original Message-----
From: Colin.Montgomery@C...
[mailto:Colin.Montgomery@C...]
Sent: 18 February 2003 12:53
To: ASP Databases
Subject: [asp_databases] Re: replace
i'm not sure what you want - do you have a field with some records which are
in the format "SR123" while others are in the format "123" ? And you want
to update the "123" ones to read "SR123" ??
If so the following SQL should do it (works in Access and I don't see any
reason for it not to work in SQL Server -apart from needing % rather than
*):
UPDATE Table1
SET Table1.myStr = "SR" & Table1.myStr
WHERE Table1.myStr Not Like "SR*"
You might need to use some ANDs or ORs in the where clause to get exactly
what you want.
If this doesn't help try to let us know what it is you want to achieve -
maybe include some sample data.
Col
-----Original Message-----
From: Pat Wong [mailto:vinyl-junkie@n...]
Sent: 18 February 2003 12:25
To: ASP Databases
Subject: [asp_databases] Re: replace
I'm a fairly new to this but I'm gonna give it a try. You don't say what you
want to replace
the letters SR with, but here's the basic format of the Replace method.
Replace(varchar,SR,whatever you want to replace SR with)
Hope this helps.
----- Original Message -----
From: <j_halstad@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, February 18, 2003 11:24 AM
Subject: [asp_databases] replace
| I need to place the letters SR in front of any numbers where those letters
| are missing in a table. Am using SQL DB and the field is varchar. I have
| looked at regular expressions but don't feel confident using them. I might
| muck everything up.
~8^) Pat Wong (ICQ #61070813)
http://www.napathon.com/
-------------------------------------------------------------------
Senility Prayer:
God grant me the senility to forget the people I never
liked anyway, the good fortune to run into the ones I
like, and the eyesight to tell the difference.
-------------------------------------------------------------------
*******
This message and any attachment are confidential and may be privileged or
otherwise protected from disclosure. If you are not the intended recipient,
please telephone or email the sender and delete this message and any
attachment from your system. If you are not the intended recipient you must
not copy this message or attachment or disclose the contents to any other
person.
For further information about Clifford Chance please see our website at
http://www.cliffordchance.com or refer to any Clifford Chance office.
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed
If you have received this e-mail in error please notify
seafish@s...
If the content is not about the business of the Sea Fish Industry Authority
or the sea fish industry then the message is neither from nor sanctioned
by the Sea Fish Industry Authority.
Working with the Seafood industry to satisfy consumers,
raise standards, improve efficiency and secure a sustainable future.
Web Site: www.seafish.co.uk
Message #7 by "Onur Kulabas" <onurkulabas@y...> on Tue, 18 Feb 2003 14:24:05
|
|
hi everyone
the last one also gives the same error, the true one is;
update CRMTBL_TASK
set CRMTBL_TASK.PROBLEM = "SR" + CRMTBL_TASK.PROBLEM
where CRMTBL_TASK.PROBLEM NOT LIKE 'SR%'
the error is on & char, in T-SQL use + instead of &.
> update reports
set reports.reportNo = "SR" & reports.reportNo
where reports.reportNo not like 'SR%'
-----Original Message-----
From: Jean Halstad [mailto:J_Halstad@S...]
Sent: Tuesday, February 18, 2003 1:47 PM
To: ASP Databases
Subject: [asp_databases] Re: replace
This is the query I wrote
update reports
set reports.reportNo = "SR" & reports.reportNo
where reports.reportNo not like "SR"
This is the error message I got
Msg 403, Level 16, State 1
Invalid operator for datatype op: boolean AND type: char
-----Original Message-----
From: Colin.Montgomery@C...
[mailto:Colin.Montgomery@C...]
Sent: 18 February 2003 12:53
To: ASP Databases
Subject: [asp_databases] Re: replace
i'm not sure what you want - do you have a field with some records which
are
in the format "SR123" while others are in the format "123" ? And you want
to update the "123" ones to read "SR123" ??
If so the following SQL should do it (works in Access and I don't see any
reason for it not to work in SQL Server -apart from needing % rather than
*):
UPDATE Table1
SET Table1.myStr = "SR" & Table1.myStr
WHERE Table1.myStr Not Like "SR*"
You might need to use some ANDs or ORs in the where clause to get exactly
what you want.
If this doesn't help try to let us know what it is you want to achieve -
maybe include some sample data.
Col
-----Original Message-----
From: Pat Wong [mailto:vinyl-junkie@n...]
Sent: 18 February 2003 12:25
To: ASP Databases
Subject: [asp_databases] Re: replace
I'm a fairly new to this but I'm gonna give it a try. You don't say what
you
want to replace
the letters SR with, but here's the basic format of the Replace method.
Replace(varchar,SR,whatever you want to replace SR with)
Hope this helps.
----- Original Message -----
From: <j_halstad@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, February 18, 2003 11:24 AM
Subject: [asp_databases] replace
| I need to place the letters SR in front of any numbers where those
letters
| are missing in a table. Am using SQL DB and the field is varchar. I have
| looked at regular expressions but don't feel confident using them. I
might
| muck everything up.
~8^) Pat Wong (ICQ #61070813)
http://www.napathon.com/
-------------------------------------------------------------------
Senility Prayer:
God grant me the senility to forget the people I never
liked anyway, the good fortune to run into the ones I
like, and the eyesight to tell the difference.
-------------------------------------------------------------------
*******
This message and any attachment are confidential and may be privileged or
otherwise protected from disclosure. If you are not the intended
recipient,
please telephone or email the sender and delete this message and any
attachment from your system. If you are not the intended recipient you
must
not copy this message or attachment or disclose the contents to any other
person.
For further information about Clifford Chance please see our website at
http://www.cliffordchance.com or refer to any Clifford Chance office.
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed
If you have received this e-mail in error please notify
seafish@s...
If the content is not about the business of the Sea Fish Industry Authority
or the sea fish industry then the message is neither from nor sanctioned
by the Sea Fish Industry Authority.
Working with the Seafood industry to satisfy consumers,
raise standards, improve efficiency and secure a sustainable future.
Web Site: www.seafish.co.uk
Message #8 by Jean Halstad <J_Halstad@S...> on Tue, 18 Feb 2003 14:25:29 -0000
|
|
Spot on! That's great. Thanks.
Jean
-----Original Message-----
From: Onur Kulabas [mailto:onurkulabas@y...]
Sent: 18 February 2003 14:24
To: ASP Databases
Subject: [asp_databases] Re: replace
hi everyone
the last one also gives the same error, the true one is;
update CRMTBL_TASK
set CRMTBL_TASK.PROBLEM = "SR" + CRMTBL_TASK.PROBLEM
where CRMTBL_TASK.PROBLEM NOT LIKE 'SR%'
the error is on & char, in T-SQL use + instead of &.
> update reports
set reports.reportNo = "SR" & reports.reportNo
where reports.reportNo not like 'SR%'
-----Original Message-----
From: Jean Halstad [mailto:J_Halstad@S...]
Sent: Tuesday, February 18, 2003 1:47 PM
To: ASP Databases
Subject: [asp_databases] Re: replace
This is the query I wrote
update reports
set reports.reportNo = "SR" & reports.reportNo
where reports.reportNo not like "SR"
This is the error message I got
Msg 403, Level 16, State 1
Invalid operator for datatype op: boolean AND type: char
-----Original Message-----
From: Colin.Montgomery@C...
[mailto:Colin.Montgomery@C...]
Sent: 18 February 2003 12:53
To: ASP Databases
Subject: [asp_databases] Re: replace
i'm not sure what you want - do you have a field with some records which
are
in the format "SR123" while others are in the format "123" ? And you want
to update the "123" ones to read "SR123" ??
If so the following SQL should do it (works in Access and I don't see any
reason for it not to work in SQL Server -apart from needing % rather than
*):
UPDATE Table1
SET Table1.myStr = "SR" & Table1.myStr
WHERE Table1.myStr Not Like "SR*"
You might need to use some ANDs or ORs in the where clause to get exactly
what you want.
If this doesn't help try to let us know what it is you want to achieve -
maybe include some sample data.
Col
-----Original Message-----
From: Pat Wong [mailto:vinyl-junkie@n...]
Sent: 18 February 2003 12:25
To: ASP Databases
Subject: [asp_databases] Re: replace
I'm a fairly new to this but I'm gonna give it a try. You don't say what
you
want to replace
the letters SR with, but here's the basic format of the Replace method.
Replace(varchar,SR,whatever you want to replace SR with)
Hope this helps.
----- Original Message -----
From: <j_halstad@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, February 18, 2003 11:24 AM
Subject: [asp_databases] replace
| I need to place the letters SR in front of any numbers where those
letters
| are missing in a table. Am using SQL DB and the field is varchar. I have
| looked at regular expressions but don't feel confident using them. I
might
| muck everything up.
~8^) Pat Wong (ICQ #61070813)
http://www.napathon.com/
-------------------------------------------------------------------
Senility Prayer:
God grant me the senility to forget the people I never
liked anyway, the good fortune to run into the ones I
like, and the eyesight to tell the difference.
-------------------------------------------------------------------
*******
This message and any attachment are confidential and may be privileged or
otherwise protected from disclosure. If you are not the intended
recipient,
please telephone or email the sender and delete this message and any
attachment from your system. If you are not the intended recipient you
must
not copy this message or attachment or disclose the contents to any other
person.
For further information about Clifford Chance please see our website at
http://www.cliffordchance.com or refer to any Clifford Chance office.
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed
If you have received this e-mail in error please notify
seafish@s...
If the content is not about the business of the Sea Fish Industry Authority
or the sea fish industry then the message is neither from nor sanctioned
by the Sea Fish Industry Authority.
Working with the Seafood industry to satisfy consumers,
raise standards, improve efficiency and secure a sustainable future.
Web Site: www.seafish.co.uk
This e-mail and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed
If you have received this e-mail in error please notify
seafish@s...
If the content is not about the business of the Sea Fish Industry Authority
or the sea fish industry then the message is neither from nor sanctioned
by the Sea Fish Industry Authority.
Working with the Seafood industry to satisfy consumers,
raise standards, improve efficiency and secure a sustainable future.
Web Site: www.seafish.co.uk
|
|
|
View Cart
