Showing posts with label varchar. Show all posts
Showing posts with label varchar. Show all posts

Wednesday, March 28, 2012

Replacing all occurrences of a string in a VARCHAR column?

Hi All
I have table Tab1 with a column Col1 of data type VARCHAR. I want to
replace every "Kop " string in that column with "HY". Is there an easy way to
do this. Below is an example with column Col1 with 2 rows of data:
Col1
---
J Dixion
K Kop
I got some helpful scripts on replacing a string within a TEXT column last
time like the one below. I have tried those scripts on other data types of
data like VARCHAR and it did not work.
http://aspfaq.com/show.asp?id=2445
Thank you in advance.MittyKom,
Try the T-SQL REPLACE function.
HTH
Jerry
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:8F17586A-F6F6-46D1-A47D-5C85F3D36E9D@.microsoft.com...
> Hi All
> I have table Tab1 with a column Col1 of data type VARCHAR. I want to
> replace every "Kop " string in that column with "HY". Is there an easy way
> to
> do this. Below is an example with column Col1 with 2 rows of data:
> Col1
> ---
> J Dixion
> K Kop
> I got some helpful scripts on replacing a string within a TEXT column last
> time like the one below. I have tried those scripts on other data types of
> data like VARCHAR and it did not work.
> http://aspfaq.com/show.asp?id=2445
> Thank you in advance.
>

Replacing all occurrences of a string in a VARCHAR column?

Hi All
I have table Tab1 with a column Col1 of data type VARCHAR. I want to
replace every "Kop " string in that column with "HY". Is there an easy way to
do this. Below is an example with column Col1 with 2 rows of data:
Col1
J Dixion
K Kop
I got some helpful scripts on replacing a string within a TEXT column last
time like the one below. I have tried those scripts on other data types of
data like VARCHAR and it did not work.
http://aspfaq.com/show.asp?id=2445
Thank you in advance.
MittyKom,
Try the T-SQL REPLACE function.
HTH
Jerry
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:8F17586A-F6F6-46D1-A47D-5C85F3D36E9D@.microsoft.com...
> Hi All
> I have table Tab1 with a column Col1 of data type VARCHAR. I want to
> replace every "Kop " string in that column with "HY". Is there an easy way
> to
> do this. Below is an example with column Col1 with 2 rows of data:
> Col1
> J Dixion
> K Kop
> I got some helpful scripts on replacing a string within a TEXT column last
> time like the one below. I have tried those scripts on other data types of
> data like VARCHAR and it did not work.
> http://aspfaq.com/show.asp?id=2445
> Thank you in advance.
>

Replacing all occurrences of a string in a VARCHAR column?

Hi All
I have table Tab1 with a column Col1 of data type VARCHAR. I want to
replace every "Kop " string in that column with "HY". Is there an easy way t
o
do this. Below is an example with column Col1 with 2 rows of data:
Col1
---
J Dixion
K Kop
I got some helpful scripts on replacing a string within a TEXT column last
time like the one below. I have tried those scripts on other data types of
data like VARCHAR and it did not work.
http://aspfaq.com/show.asp?id=2445
Thank you in advance.UPDATE tab1
SET col1 = REPLACE(col1,'Kop','HY')
WHERE col1 LIKE '%Kop%' ;
David Portas
SQL Server MVP
--|||Thank you Dave. It got the job. Thank you once again.
--
5 years experience with SQL Server 2000 and SAP BW/SEM
"David Portas" wrote:

> UPDATE tab1
> SET col1 = REPLACE(col1,'Kop','HY')
> WHERE col1 LIKE '%Kop%' ;
> --
> David Portas
> SQL Server MVP
> --
>
>

Replacing all occurrences of a string in a VARCHAR column?

Hi All
I have table Tab1 with a column Col1 of data type VARCHAR. I want to
replace every "Kop " string in that column with "HY". Is there an easy way t
o
do this. Below is an example with column Col1 with 2 rows of data:
Col1
---
J Dixion
K Kop
I got some helpful scripts on replacing a string within a TEXT column last
time like the one below. I have tried those scripts on other data types of
data like VARCHAR and it did not work.
http://aspfaq.com/show.asp?id=2445
Thank you in advance.MittyKom,
Try the T-SQL REPLACE function.
HTH
Jerry
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:8F17586A-F6F6-46D1-A47D-5C85F3D36E9D@.microsoft.com...
> Hi All
> I have table Tab1 with a column Col1 of data type VARCHAR. I want to
> replace every "Kop " string in that column with "HY". Is there an easy way
> to
> do this. Below is an example with column Col1 with 2 rows of data:
> Col1
> ---
> J Dixion
> K Kop
> I got some helpful scripts on replacing a string within a TEXT column last
> time like the one below. I have tried those scripts on other data types of
> data like VARCHAR and it did not work.
> http://aspfaq.com/show.asp?id=2445
> Thank you in advance.
>

Friday, March 23, 2012

replace problem

Hi,
We hae a varchar column that has data values stored like:
'abc645abc56'
'adc015adc56'
and we need to extract only the numeric values from this
column for example for above 2 rows:
'64556'
'01556'
i am trying to use replace but is not allowing me to use
patters like select replace(column,'[A-Z]','')
How to use replace or any other function to solve this ?
Thanks
--HarvinderCheck each character and see if is numeric if not dump it.
Here is a function that I use. Run the script on the server in the right
database. To call it just do:
select StripNonNumeric(fieldname) from tablename
CREATE FUNCTION [dbo].[StripNonNumeric] (@.strIn varchar(30))
RETURNS FLOAT AS
BEGIN
declare @.intCounter int
declare @.strTmp varchar(300)
declare @.chrTmp varchar(1)
set @.intCounter = 1
set @.strTmp = ''
WHILE @.intCounter <Len(@.strIn)+1
BEGIN
set @.chrTmp = substring(@.strIn,@.intCounter,1)
if @.chrTmp NOT LIKE '%[^0123456789]%'
begin
set @.strTmp = @.strTmp + @.chrTmp
End
set @.intCounter =@.intCounter +1
if @.strTmp = ''
begin
set @.strTmp = null
end
END
return @.strTmp
END
"harvinder" <hs@.metratech.com> wrote in message
news:50d801c3417c$b99844a0$a401280a@.phx.gbl...
> Hi,
> We hae a varchar column that has data values stored like:
> 'abc645abc56'
> 'adc015adc56'
> and we need to extract only the numeric values from this
> column for example for above 2 rows:
> '64556'
> '01556'
> i am trying to use replace but is not allowing me to use
> patters like select replace(column,'[A-Z]','')
> How to use replace or any other function to solve this ?
> Thanks
> --Harvinder
>|||is yours better than mine?
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OS1YIIYQDHA.2320@.TK2MSFTNGP12.phx.gbl...
> Nothing built in using *just* replace, but you could create a function and
> call that inline:
>
> CREATE FUNCTION dbo.makeNumeric
> (
> @.f VARCHAR(32)
> )
> RETURNS VARCHAR(32)
> AS
> BEGIN
> DECLARE @.p TINYINT
> SET @.p = PATINDEX('%[^0-9]%', @.f)
> WHILE @.p > 0
> BEGIN
> SET @.f = STUFF(@.f, @.p, 1, '')
> SET @.p = PATINDEX('%[^0-9]%', @.f)
> END
> RETURN @.f
> END
> GO
> SELECT dbo.makeNumeric('abc645abc56')
> GO
> DROP FUNCTION dbo.makeNumeric
> GO
>
> "harvinder" <hs@.metratech.com> wrote in message
> news:50d801c3417c$b99844a0$a401280a@.phx.gbl...
> > Hi,
> >
> > We hae a varchar column that has data values stored like:
> > 'abc645abc56'
> > 'adc015adc56'
> >
> > and we need to extract only the numeric values from this
> > column for example for above 2 rows:
> > '64556'
> > '01556'
> >
> > i am trying to use replace but is not allowing me to use
> > patters like select replace(column,'[A-Z]','')
> > How to use replace or any other function to solve this ?
> >
> > Thanks
> > --Harvinder
> >
>|||What does "better" mean?
I have no idea. I really haven't looked at yours.
If you are slyly asking why I posted a function when you already had, I
didn't see your post yet until I refreshed my list, *after* I had posted
mine.
"Tammy B." <Tb@.stinkylips.com> wrote in message
news:#G5JWLYQDHA.4024@.tk2msftngp13.phx.gbl...
> is yours better than mine?|||No i wasn't asking anything slyly, I was asking you to look at mine.
If yours is better then I will use yours.
You are the MVP.
get some rest
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:edMVqdYQDHA.560@.TK2MSFTNGP10.phx.gbl...
> What does "better" mean?
> I have no idea. I really haven't looked at yours.
> If you are slyly asking why I posted a function when you already had, I
> didn't see your post yet until I refreshed my list, *after* I had posted
> mine.
>
>
> "Tammy B." <Tb@.stinkylips.com> wrote in message
> news:#G5JWLYQDHA.4024@.tk2msftngp13.phx.gbl...
> > is yours better than mine?
>|||better means it works better. You should get some rest and look it up.
The isnumeric part can be tricky because $ can be numeric with isnumeric and
I went through several versions, but I don't have an MVP that I like to tack
on to my posts.
I thought you might have a better version.
Gee It never occured to me that you might have posted something at the same
time as me. This is my first newsgroup. This is my first moment on earth.
Thanks for pointing stuff out to me.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:edMVqdYQDHA.560@.TK2MSFTNGP10.phx.gbl...
> What does "better" mean?
> I have no idea. I really haven't looked at yours.
> If you are slyly asking why I posted a function when you already had, I
> didn't see your post yet until I refreshed my list, *after* I had posted
> mine.
>
>
> "Tammy B." <Tb@.stinkylips.com> wrote in message
> news:#G5JWLYQDHA.4024@.tk2msftngp13.phx.gbl...
> > is yours better than mine?
>|||You are an idiot
"Tammy B." <Tb@.stinkylips.com> wrote in message
news:uDHwHoZQDHA.1552@.TK2MSFTNGP10.phx.gbl...
> better means it works better. You should get some rest and look it up.
> The isnumeric part can be tricky because $ can be numeric with isnumeric
and
> I went through several versions, but I don't have an MVP that I like to
tack
> on to my posts.
> I thought you might have a better version.
> Gee It never occured to me that you might have posted something at the
same
> time as me. This is my first newsgroup. This is my first moment on
earth.
> Thanks for pointing stuff out to me.
>
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:edMVqdYQDHA.560@.TK2MSFTNGP10.phx.gbl...
> > What does "better" mean?
> >
> > I have no idea. I really haven't looked at yours.
> >
> > If you are slyly asking why I posted a function when you already had, I
> > didn't see your post yet until I refreshed my list, *after* I had posted
> > mine.
> >
> >
> >
> >
> > "Tammy B." <Tb@.stinkylips.com> wrote in message
> > news:#G5JWLYQDHA.4024@.tk2msftngp13.phx.gbl...
> > > is yours better than mine?
> >
> >
>|||How can you prove that you don't know what "better" means by giving me
definitions of "better".
what an idiot.
I explained the context in which I wanted to find code that may be better
than mine. If it is better in other ways, so much the better you idiot.
I said that the isnumeric part can be tricky because $ can be numeric with
isnumeric.
If the code is better in this respect I would use it in place of mine.
There has been much debate about this issue - none of which you are aware
of; the discussion was far boader than what you covered in your MVP
workbook.
Yes, why don't you get back to me with how your code stacks up according to
each of the definitions that you have provided.
I'll be waiting up.
You should not represent yourself as being valuable to Microsoft; when you
are just poo-pooing in the pool.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23OVGFxZQDHA.2424@.tk2msftngp13.phx.gbl...
> > better means it works better.
> I still don't understand what "works better" means. Less code? Less
> complicated code? Faster? More reliable? Covers more borderline cases?
> Meets this specific poster's needs?
> > but I don't have an MVP that I like to tack
> > on to my posts.
> It's not something I tack on to my posts. It's something I earned. Look
it
> up. http://mvp.support.microsoft.com/
>|||> what an idiot.
Welcome to my killfile. I don't know what I did to so greatly offend you,
but if all you can do is call me names, I have absolutely no interest in
carrying on a conversation with you. Have a great weekend.

Replace multiple spaces in varchar

How can I replace multiple spaces in a varchar string?
Given 'ABC DEF GHI' I want to return 'ABC DEF GHI'
I want to find all occurrences of 2 or more spaces and replace with 1 space.
My reporting application can't handle this.
Thankstry this
replace( 'ABC DEF GHI', ' ',' ')
"Terri" <terri@.cybernets.com> wrote in message
news:d3ot2k$p63$1@.reader2.nmix.net...
> How can I replace multiple spaces in a varchar string?
> Given 'ABC DEF GHI' I want to return 'ABC DEF GHI'
> I want to find all occurrences of 2 or more spaces and replace with 1
> space.
> My reporting application can't handle this.
> Thanks
>|||--One possibility:
SELECT REPLACE(REPLACE('ABC DEF GHI JKL', ' ', ' '), ' ', ' ')
Handles up to 4 spaces
SELECT REPLACE(REPLACE(REPLACE('ABC DEF GHI JKL', ' ', '
'), ' ', ' '), ' ', ' ')
Handles up to 8 spaces
I know this works on my setup but maybe an expert will see a problem with
this.
Another (probably slower but will handle any number of spaces)
would be to build a function to handle this and integrate this logic:
DECLARE @.str VARCHAR(50)
SET @.str = 'ABC DEF GHI JKL'
WHILE LEN(@.str) > LEN(REPLACE(@.str, ' ', ' '))
SET @.str = REPLACE(@.str, ' ', ' ')
SELECT @.str
"Terri" <terri@.cybernets.com> wrote in message
news:d3ot2k$p63$1@.reader2.nmix.net...
> How can I replace multiple spaces in a varchar string?
> Given 'ABC DEF GHI' I want to return 'ABC DEF GHI'
> I want to find all occurrences of 2 or more spaces and replace with 1
> space.
> My reporting application can't handle this.
> Thanks
>|||"Terri" <terri@.cybernets.com> wrote in message
news:d3ot2k$p63$1@.reader2.nmix.net...
> How can I replace multiple spaces in a varchar string?
> Given 'ABC DEF GHI' I want to return 'ABC DEF GHI'
> I want to find all occurrences of 2 or more spaces and replace with 1
> space.
> My reporting application can't handle this.
> Thanks
>
SELECT REPLACE(REPLACE(REPLACE('ABC DEF GHI',' ','[ ]'),'][ ',''),'[ ]','
')|||The short answer is to nest REPLACE() functions inside each other.
This problem came up years ago in a Newsgroup, but we then asked the
question; What is the best series of replacement sizes to use on a
string of length(n)? For example, I could use powers of 2; reduce 8
spaces to 1, then 4 spaces to 1, and finally 2 spaces to 1, which does
not work unless you run it twice). I guessed that a Fibbonacci series
might be best but could not prove it. There were some other guesses,
but one guy worked out a list by brute force.
Anyone know where to find this? Since newbies constantly make
everything in the universe VARCHAR(50), that sounds like a good upper
value to use for experiments.|||Never mind; I found it! It is over in comp.database.theory as
"Squeezing spaces out of a string" and there is some code posted for
the solutions.|||http://tinyurl.com/cx7gt
HTH,
Gert-Jan
Terri wrote:
> How can I replace multiple spaces in a varchar string?
> Given 'ABC DEF GHI' I want to return 'ABC DEF GHI'
> I want to find all occurrences of 2 or more spaces and replace with 1 spac
e.
> My reporting application can't handle this.
> Thanks|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1113589959.961601.150610@.f14g2000cwb.googlegroups.com...
> Never mind; I found it! It is over in comp.database.theory as
> "Squeezing spaces out of a string" and there is some code posted for
> the solutions.
>
Interesting thread. Here's a rewrite of my earlier response to take into
account overflow:
DECLARE @.s VARCHAR(8000)
SET @.s = 'a b c d' + SPACE(7989) + 'e'
SELECT
REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,000
1,2000),' ','[ ]'),'][ ',''),'[ ]','
') +
REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,200
1,2000),' ','[ ]'),'][ ',''),'[ ]','
') +
REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,400
1,2000),' ','[ ]'),'][ ',''),'[ ]','
') +
REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,600
1,2000),' ','[ ]'),'][ ',''),'[ ]','
'),
SPACE(4),' '),SPACE(3),' '),SPACE(2),' ')|||Hmm, why use 15 replaces when you only need 6?
See http://tinyurl.com/cx7gt
Gert-Jan
Chris Hohmann wrote:
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1113589959.961601.150610@.f14g2000cwb.googlegroups.com...
> Interesting thread. Here's a rewrite of my earlier response to take into
> account overflow:
> DECLARE @.s VARCHAR(8000)
> SET @.s = 'a b c d' + SPACE(7989) + 'e'
> SELECT
> REPLACE(REPLACE(REPLACE(
> REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,000
1,2000),' ','[ ]'),'][ ',''),'[ ]'
,'
> ') +
> REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,200
1,2000),' ','[ ]'),'][ ',''),'[ ]'
,'
> ') +
> REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,400
1,2000),' ','[ ]'),'][ ',''),'[ ]'
,'
> ') +
> REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,600
1,2000),' ','[ ]'),'][ ',''),'[ ]'
,'
> '),
> SPACE(4),' '),SPACE(3),' '),SPACE(2),' ')|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:426033FB.8CBC2FC0@.toomuchspamalready.nl...
> Hmm, why use 15 replaces when you only need 6?
> See http://tinyurl.com/cx7gt
> Gert-Jan
>
> Chris Hohmann wrote:
The encoding from ' ' to '[ ]' has the potential to increase the size of the
string threefold. The maximum allowable length for stings is 8000. Since
8000 is not evenly divisible by 3, the string needs to be broken into at
least four parts. Once multiple spaces have been collapsed in each of the
four parts, the resulting strings are concatenated together. However, it is
possible that one part ends with a space and the following part begins with
one. The limit case for this scenario would be a string of 8000 spaces. In
which case four spaces would be concatenated together at the end. To account
for this case, I perform the three additional replace calls after the parts
have been concatenated. You could actually get away with two replace calls
at the end instead of three and eliminate the calls to SPACE. Something like
REPLACE(REPLACE
.
.
.
' ',' '),' ',' ')
In which case there would only be 14 replace calls.
HTH
-Chris Hohmann

Wednesday, March 21, 2012

REPLACE function needed to remove single quotes from an output

I have a variable @.var1 of type varchar(100) which captures the following
error message, which is produced by executing a sql statement:
Incorrect syntax near the keyword 'where'.
Please pay special attention to the single quotes around the word where.
These single quotes are causing a lot of problems when I include the above
error message as-is in XML processing.
If I remove both the above single quotes manually, things work fine.
Also, if I change them to two single quotes, things also work fine. But
since this message is captured programatically, I cannot manipulate it in
these two ways.
Alternatively, I decided to remove these two single quotes around the word
where. I tried using the following but did not succeed.
I am using the REPLACE function, which has the following syntax: REPLACE (
'string_expression1' , 'string_expression2' , 'string_expression3' )
So, in my case: select REPLACE(@.var1, '', ' ')
In the above expression, I am basically attempting to replace the single
quotes with a space.
Please note that the string_expression2 is two single quotes.
string_expression3 is a single quote followed by a space and then followed
by single quote.
This statement produces another error: An expression of non-boolean type
specified in a context where a condition is expected, near 'select'.
Basically, if re-run this same replace by changing single quotes to two
single quotes, the above replace statement does not produce an error.
Could somebody help me get a working REPLACE statement which will give me my
original error message free of single quotes in the following form.
Incorrect syntax near the keyword where.
Appreciate your help.
ThanksHB
Can you show us @.var1 ?
"HB" <nospam@.microsoft.com> wrote in message
news:%232SpQF5qFHA.1788@.tk2msftngp13.phx.gbl...
>I have a variable @.var1 of type varchar(100) which captures the following
> error message, which is produced by executing a sql statement:
> Incorrect syntax near the keyword 'where'.
> Please pay special attention to the single quotes around the word where.
> These single quotes are causing a lot of problems when I include the above
> error message as-is in XML processing.
> If I remove both the above single quotes manually, things work fine.
> Also, if I change them to two single quotes, things also work fine. But
> since this message is captured programatically, I cannot manipulate it in
> these two ways.
> Alternatively, I decided to remove these two single quotes around the word
> where. I tried using the following but did not succeed.
> I am using the REPLACE function, which has the following syntax: REPLACE (
> 'string_expression1' , 'string_expression2' , 'string_expression3' )
> So, in my case: select REPLACE(@.var1, '', ' ')
> In the above expression, I am basically attempting to replace the single
> quotes with a space.
> Please note that the string_expression2 is two single quotes.
> string_expression3 is a single quote followed by a space and then followed
> by single quote.
> This statement produces another error: An expression of non-boolean type
> specified in a context where a condition is expected, near 'select'.
> Basically, if re-run this same replace by changing single quotes to two
> single quotes, the above replace statement does not produce an error.
> Could somebody help me get a working REPLACE statement which will give me
> my
> original error message free of single quotes in the following form.
> Incorrect syntax near the keyword where.
> Appreciate your help.
> Thanks
>|||Hello, HB
To remove the single quotes from a string, use:
REPLACE(@.var1, '''', ' ')
(there are 4 quotes as the second parameter; that is because to specify
a single quote in a string, you have to type a quote twice, for
example: 'McDonald''s')
A better idea would be to properly use XML escape sequences for any
special characters, i.e: instead of "<" use "<", instead of a single
quote, use "'", etc. If we consider only the standard special
characters, we can use:
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
@.var,
'&','&'),'<','<'),'>','>'),'"','"'),'''',''')
But we must also consider that there are accented characters, which
should also be escaped in an XML string, so the best way would be to
construct the XML string using a XML parser (like MSXML), using the
DOM, for example.
Razvan|||Razvan,
Thanks so much for your suggestions.
I am trying to construct the XML string in SQL. Given that SQL is limited
(as far as processing strings) compared to pure programming languages (C++,
C# etc.), what do you suggest I should do to accomplish the escaping of
various characters, including the special ones, like you wrote in your post?
Like I mentioned in my previous post, I am capturing the error in a SQL
stored procedure and want to ship out that error message as a valid (free
from the characters you mentioned) XML string. So, if possible I would like
to accomplish your suggestions in SQL itself. If you can provide help as to
how to achieve escaping various characters using a general purpose approach
(using DOM or otherwise), that will be great.
Reply appreciated
Thanks again Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1125210516.602885.58280@.g43g2000cwa.googlegroups.com...
> Hello, HB
> To remove the single quotes from a string, use:
> REPLACE(@.var1, '''', ' ')
> (there are 4 quotes as the second parameter; that is because to specify
> a single quote in a string, you have to type a quote twice, for
> example: 'McDonald''s')
> A better idea would be to properly use XML escape sequences for any
> special characters, i.e: instead of "<" use "<", instead of a single
> quote, use "'", etc. If we consider only the standard special
> characters, we can use:
> SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
@.var,
> '&','&'),'<','<'),'>','>'),'"','"'),'''',''')
> But we must also consider that there are accented characters, which
> should also be escaped in an XML string, so the best way would be to
> construct the XML string using a XML parser (like MSXML), using the
> DOM, for example.
> Razvan
>

Replace first 3 digit of phone numbers

Hi All,

I am trying to replace only first 3-digit of phone number starting with 011 to 000.

CREATETABLE #temp(stringRep VARCHAR(60))

INSERTINTO #temp(stringRep)VALUES('011-203-0011')

INSERTINTO #temp(stringRep)VALUES('011-203-2333')

DECLARE @.startPos INT

SET @.startPos = 1

UPDATE #temp SET stringRep =REPLACE(stringRep,SUBSTRING(stringRep, 1,

3),'000')

SELECT*FROM #temp

DROPTABLE #temp

stringRep

000-203-0000
000-203-2333

it also replaces the last three digit with 000. the result i would like to get is

stringRep

000-203-0011
000-203-2333

can anybody advise me on this? thanks!

Try:

update #temp

set stringRep = stuff(stringRep, 1, 3, '000')

where stringRep like '011%'

AMB

sql

Replace Errors with NULL using Convert

I have a table with a varchar field that for the most part contains valid
dates ie (mm/dd/yyyy). There are some items that are not dates. I would
like a SQL statement that converts the varchar to a datetime and where there
is an error for a particular field will return a null for that field.
For example if my table contains the following items in Field1:
2/1/2005
1/1/2004
other data
3/1/2005
The query should return:
2005-2-1 00:00:00
2004-1-1 00:00:00
NULL
2005-3-1 00:00:00
If I use:
SELECT convert(datetime, Field1) as Field1
FROM table1
Then I get a conver error. Any ideas or suggestions would be helpful.
Thanks!
Dan,
See if this works:
select
case when ISDATE(Field1) = 1
then cast(Field1 as datetime)
else NULL end
from table1
If you can't live with the fact that some garbled
data may convert unexpectedly (the string '110919'
will convert to September 19, 2011, for example),
you'll have to do some pattern matching of your own
as well, such as
case when Field1 like '%/%/%' and Field1 not like '%/%/%/%' ...
and isdate(Field1) = 1 then ...
Steve Kass
Drew University
Dan wrote:

>I have a table with a varchar field that for the most part contains valid
>dates ie (mm/dd/yyyy). There are some items that are not dates. I would
>like a SQL statement that converts the varchar to a datetime and where there
>is an error for a particular field will return a null for that field.
>For example if my table contains the following items in Field1:
>2/1/2005
>1/1/2004
>other data
>3/1/2005
>The query should return:
>2005-2-1 00:00:00
>2004-1-1 00:00:00
>NULL
>2005-3-1 00:00:00
>If I use:
>SELECT convert(datetime, Field1) as Field1
>FROM table1
>Then I get a conver error. Any ideas or suggestions would be helpful.
>Thanks!
>
>