Replacing Multiple Strings Using the REPLACE Function

I'm would like to replace all occurrences of "99999" and "-99999" with "" in a column using SSIS. I can use the REPLACE function in a Derived Column to replace one of those strings, for example: REPLACE(mycolumn,"99999",""). Or to replace both I could use REPLACE(REPLACE(mycolumn,"-99999",""),"99999",""). This seems kind of cumbersome and would get very complicated if I were replacing more strings with "". I'm guessing there is a better way. Can anyone help me out?

Thanks,
Ridium

Ridium wrote:

I'm would like to replace all occurrences of "99999" and "-99999" with "" in a column using SSIS. I can use the REPLACE function in a Derived Column to replace one of those strings, for example: REPLACE(mycolumn,"99999",""). Or to replace both I could use REPLACE(REPLACE(mycolumn,"-99999",""),"99999",""). This seems kind of cumbersome and would get very complicated if I were replacing more strings with "". I'm guessing there is a better way. Can anyone help me out?

Thanks,
Ridium

There isn't a simpler way, that is exactly how you should do it. Its simple and it works and I don't think its cumbersome at all. Just my opinion.

What syntax do you envisage for a REPLACE function that allows you to replace multiple strings? Also, in your example given above I can envisage it replacing the "99999" part of "-99999" and you being left with "-" which isn't what you want.

-Jamie

|||I have about 20 non-printable characters I want to scrub from my data. I guess I will need to string 20 REPLACE functions together unless someone has a better idea.

Thanks for you help,
Ridium
|||

Ridium wrote:

I have about 20 non-printable characters I want to scrub from my data. I guess I will need to string 20 REPLACE functions together unless someone has a better idea.

Thanks for you help,
Ridium

Yeah, I think that's what you'll have to do. Why is that such a problem? I honestly can't fathom how this could be less (in your words) "cumbersome". I'm interested in any ideas you may have.

Regards

Jamie

|||

Jamie Thomson wrote:

Ridium wrote:

I have about 20 non-printable characters I want to scrub from my data. I guess I will need to string 20 REPLACE functions together unless someone has a better idea.

Thanks for you help,
Ridium

Yeah, I think that's what you'll have to do. Why is that such a problem? I honestly can't fathom how this could be less (in your words) "cumbersome". I'm interested in any ideas you may have.

Regards

Jamie

I discovered a more convenient method. Instead of using all those REPLACE functions, just use an expression:
mycolumn == "99999" || mycolumn == "-99999" ? NULL(DT_DECIMAL,2) : (DT_CY)mycolumn

I can just add an additional "or" operation for each new term I want to search for. This is also less prone to errors.

Ridium
|||

Ridium wrote:

Jamie Thomson wrote:

Ridium wrote:

I have about 20 non-printable characters I want to scrub from my data. I guess I will need to string 20 REPLACE functions together unless someone has a better idea.

Thanks for you help,
Ridium

Yeah, I think that's what you'll have to do. Why is that such a problem? I honestly can't fathom how this could be less (in your words) "cumbersome". I'm interested in any ideas you may have.

Regards

Jamie

I discovered a more convenient method. Instead of using all those REPLACE functions, just use an expression:
mycolumn == "99999" || mycolumn == "-99999" ? NULL(DT_DECIMAL,2) : (DT_CY)mycolumn

I can just add an additional "or" operation for each new term I want to search for. This is also less prone to errors.

Ridium

OK, glad you found something that you're happy with. A word of warning though, use parentheses around the first argument to the conditional operator or else you could find yourself in a world of hurt.

Why do you think that is less prone to errors? And when you say "just use an expression", why would using the REPLACE function not constitute using an expression?

Regards

-Jamie

|||I meant use a conditional expression. It seems pretty obvious that something like this:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A,B,C),D,E),F,G),H,I),J,K)

Is a lot more complicated than my solution. Imagine trying to include 20 or 30 functions. This is much harder to read and understand. You could easily lose sight of what parameter goes to what REPLACE function which might cause an error.

Ridium
|||

Ridium wrote:

I meant use a conditional expression. It seems pretty obvious that something like this:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A,B,C),D,E),F,G),H,I),J,K)

Is a lot more complicated than my solution. Imagine trying to include 20 or 30 functions. This is much harder to read and understand. You could easily lose sight of what parameter goes to what REPLACE function which might cause an error.

Ridium

OK fair enough, can't argue with that. Note what I said about parentheses though!

-Jamie

Replacing column value on insert

Hello SQLServer-specialists!
I'm running a database which contains on table, where the customer is
allowed to import data into.
This table also has a column, which the customer should set to his name, so
that you know who imported the record.
The problem is, that the customer is not restricted in setting this columns
value, so he also could set it to 'foo'!
Now I want to ensure, that the value is always set to the logged on user,
when importing data.
I know, that using a view and an INSTEAD OF trigger would do the job.
But as the table has abount 250 columns, I try to find an easier way.
Is it possible to write a trigger, which executes just before the insert,
and is able to replace a columns value?
Thanks in advance!
MaxHi there,
why not setting up a UPDATE / INSERT trigger then modifying the users
name, like this one below which takes the SUSER_SNAME as a input, make
sure you modify that, to eventually reflect your enviroment:
CREATE TRIGGER SomeTrigger ON SOmeTable
FOR INSERT
AS
BEGIN
UPDATE SomeTable SET ModifiedColumn = SUSER_SNAME
FROM SomeTable S
INNER JOIN INSERTED I
ON S.PKColumn = I.PKColumn
END
HTH, Jens Suessmeyer,
http://www.sqlserver2005.de
--|||Markus,
You can use the system function SYSTEM_USER to get the current username.
You can create an AFTER Trigger for inserts and update the inserted rows
Username with the SYSTEM_USER function.
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Markus Emayr" <essmayr/at/racon-linz.at> wrote in message
news:OqyIvYMTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> Hello SQLServer-specialists!
> I'm running a database which contains on table, where the customer is
> allowed to import data into.
> This table also has a column, which the customer should set to his name,
> so that you know who imported the record.
> The problem is, that the customer is not restricted in setting this
> columns value, so he also could set it to 'foo'!
> Now I want to ensure, that the value is always set to the logged on user,
> when importing data.
> I know, that using a view and an INSTEAD OF trigger would do the job.
> But as the table has abount 250 columns, I try to find an easier way.
> Is it possible to write a trigger, which executes just before the insert,
> and is able to replace a columns value?
> Thanks in advance!
> Max
>|||Hello!
Thanks for your answers!
I now tried the following
CREATE TRIGGER ModificationTrigger ON TheTableToInsertTo
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO TheTableToInsertTo
SELECT col1, col2, suser_sname()
FROM inserted
END
First I thought, that inserting into the same table, as the trigger should
instead of an insert, could make problems, but it works perfectly!!!
Greetings,
Max
"Markus Emayr" <essmayr/at/racon-linz.at> schrieb im Newsbeitrag
news:OqyIvYMTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> Hello SQLServer-specialists!
> I'm running a database which contains on table, where the customer is
> allowed to import data into.
> This table also has a column, which the customer should set to his name,
> so that you know who imported the record.
> The problem is, that the customer is not restricted in setting this
> columns value, so he also could set it to 'foo'!
> Now I want to ensure, that the value is always set to the logged on user,
> when importing data.
> I know, that using a view and an INSTEAD OF trigger would do the job.
> But as the table has abount 250 columns, I try to find an easier way.
> Is it possible to write a trigger, which executes just before the insert,
> and is able to replace a columns value?
> Thanks in advance!
> Max
>

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.
>

Replacing a portion of text string in column

I need to replace a portion of a url in a column as a result of
changing servers. Is there a SELECT/REPLACE/UPDATE combination query
that can do this. The table has close to a thousand entries and would
be nice if a query can be set to do this. Tried the REPLACE example
in the BOOKS ONLINE but it creates syntax error, apparently because it
does not like the characters in the url and/or wildcards. I don't need
to replace the entire url, only the portion before ".com". Thanks in
anticipation of your help.

Pradip SagdeoPradip,
This statement:
select replace('http://www.technicalvideos.net','s.net','s14.net')
seems to work OK, so, perhaps you could give your exact update and the exact
error along with some sample data.
Best regards,
Chuck Conover
www.TechnicalVideos.net

"Pradip Sagdeo" <pradip.m.sagdeo@.pfizer.com> wrote in message
news:8dbc5a0f.0401280726.708a1c6@.posting.google.co m...
> I need to replace a portion of a url in a column as a result of
> changing servers. Is there a SELECT/REPLACE/UPDATE combination query
> that can do this. The table has close to a thousand entries and would
> be nice if a query can be set to do this. Tried the REPLACE example
> in the BOOKS ONLINE but it creates syntax error, apparently because it
> does not like the characters in the url and/or wildcards. I don't need
> to replace the entire url, only the portion before ".com". Thanks in
> anticipation of your help.
> Pradip Sagdeo|||Thanks. I will try again. Must have made a typing mistake.

Pradip

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Pradip Sagdeo (pradip.m.sagdeo@.pfizer.com) writes:
> I need to replace a portion of a url in a column as a result of
> changing servers. Is there a SELECT/REPLACE/UPDATE combination query
> that can do this. The table has close to a thousand entries and would
> be nice if a query can be set to do this. Tried the REPLACE example
> in the BOOKS ONLINE but it creates syntax error, apparently because it
> does not like the characters in the url and/or wildcards. I don't need
> to replace the entire url, only the portion before ".com". Thanks in
> anticipation of your help.

Unfortunately, the replace() function does not support wildcards, so
if you need to use that, you have to be creative. Or descend to use
some client code to handle it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Replacing a column with a foreign key

In an *existing* database, how would you remove the City column in theEmployees table, and put in the CityID key column from the Cities table?first you would add a cityid column with alter table add column syntax.
Then to add the data, you could do a join with cities table.

UPDATE
Employees
SET
Cityid = C.CityID
FROM
Employees E
INNER JOIN Cities C
ON E.city = C.city

Then, removing the city column should be as simple as alter table remove column.|||

The following simplified version also works:

UPDATE Employees
SET Cityid = C.CityID
FROM Cities C
WHERE Employees.City = C.City

Replacing a character

Hi

I have a table with column type as ntext. I need to modify the column value. I wanted to replace a given character\string with another one in this column. Any assistance on this is highly appreciated.

Thanks!

Santhosh

Santhosh,

1) If the text in the column is not too long, you can cast ntext to nvarchar and use replace function.

2) check this link http://www.webpowersoftware.co.uk/FORUMS/ShowPost.aspx?PostID=336

rgds,

v r kumar

replacing [XFO=BM] in a column

Hi,
I have a table that contains text columns with data containing some tags
like [XFO=BM] in it.
I'd like to remove these and am using a view that contains calls to a
function to do this, but it doesn't work if the tags have square brackets
surrounding them.
i.e. set @.working = replace (@.working, '[XFO=DN]', '')
doesn't work.
Does anyone know how I can do this, or how I need to delimit the []s to make
the tags vanish?
Thanks in advance,
IanYou need to escape the opening bracket:
set @.working = replace (@.working, '[[]XFO=DN]', '')
ML
http://milambda.blogspot.com/|||Not sure i've understood correctly as the following works for me on SQL2k
DECLARE @.WORKING VARCHAR(50)
SET @.wORKING = '[XFO=DN]'
SELECT replace (@.working, '[XFO=DN]', '')
Does the problem lie in the function ...? Are you using LIKE anywhere ...?
From Books Online
Symbol
Meaning
LIKE '[ [ ]' [
HTH. Ryan
"Ian Jagger" <IanJagger@.discussions.microsoft.com> wrote in message
news:C3850ACE-88F8-437E-94C5-D5FCD83DAEE9@.microsoft.com...
> Hi,
> I have a table that contains text columns with data containing some tags
> like [XFO=BM] in it.
> I'd like to remove these and am using a view that contains calls to a
> function to do this, but it doesn't work if the tags have square brackets
> surrounding them.
> i.e. set @.working = replace (@.working, '[XFO=DN]', '')
> doesn't work.
> Does anyone know how I can do this, or how I need to delimit the []s to
> make
> the tags vanish?
> Thanks in advance,
> Ian|||
"ML" wrote:

> You need to escape the opening bracket:
> set @.working = replace (@.working, '[[]XFO=DN]', '')
Thanks for that, only unfortunately that didn't work. Delimiting both sets
of brackets didn't work either.
Any other ideas?
Ian|||Sorry, my bad. Ryan has a better answer. What was I thinking? No one knows.
ML
http://milambda.blogspot.com/|||
> Not sure i've understood correctly as the following works for me on SQL2k
> DECLARE @.WORKING VARCHAR(50)
> SET @.wORKING = '[XFO=DN]'
> SELECT replace (@.working, '[XFO=DN]', '')
> Does the problem lie in the function ...?
Hmmm, well the function although long is relatively straightforward...
ALTER FUNCTION convertPropertyTitle (@.inp text)
returns varchar (8000)
as
begin
if @.inp is null
return ' '
declare @.working varchar (8000)
set @.working = replace (cast (@.inp as varchar (8000)),
'{\rtf1\ansi\ansicpg1252\deff0\deflang10
33', '')
set @.working = replace (@.working, '{\rtf1\ansi\deff0{\fonttbl{\f0\fnil MS
Sans Serif;}}', '')
set @.working = replace (@.working,
'\rtf1\ansi\deff0{\fonttbl{\f0\fnil\fprq
2\fcharset0 Times New Roman;', '')
set @.working = replace (@.working, '{\fonttbl{\f0\fnil MS Sans Serif;}}', '')
set @.working = replace (@.working, '{\fonttbl{\f0\fnil MS Sans Serif;', '')
set @.working = replace (@.working, '{\f1\fnil\fcharset0 MS Sans Serif;}}', ''
)
set @.working = replace (@.working, '\fonttbl{\f0\fnil\fcharset0 MS Sans
Serif;}{\f1\fnil MS Sans Serif;', '')
set @.working = replace (@.working, '\fonttbl{\f0\fnil\fprq2\fcharset0 Times
New Roman;', '')
set @.working = replace (@.working, '\f1\fnil MS Sans Serif;', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\f0\fs16', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\b\f0\fs16', '<b>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\lang1033\b\f0\fs16'
,
'<b>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\ul\b\f0\fs16',
'<u><b>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\ul\f0\fs16', '<u>')
set @.working = replace (@.working, '\viewkind4\uc1 d\i\fs16', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\i\f0\fs16', '<i>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\ul\b\i\f0\fs16',
'<u><b><i>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\b\i\f0\fs16', '<b><i>
')
set @.working = replace (@.working, '\viewkind4\uc1\pard\f0\fs24', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\lang2057\f0\fs20', ''
)
set @.working = replace (@.working, '\viewkind4\uc1\pard\f0\fs20', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\lang2057\b\f0\fs16'
,
'<b>')
set @.working = replace (@.working, '}', '')
set @.working = replace (@.working, '{', '')
set @.working = replace (@.working, ' ', '')
set @.working = replace (@.working, '\ulnone', '</u>')
set @.working = replace (@.working, '\ul', '<u>')
set @.working = replace (@.working, '\lang2057\b\f1', '<b>')
set @.working = replace (@.working, '\lang1033\b\f0', '<b>')
set @.working = replace (@.working, '\lang1033', '')
set @.working = replace (@.working, '\lang2057', '')
set @.working = replace (@.working, '\lang2057\fs20', '')
set @.working = replace (@.working, '\b0', '</b>')
set @.working = replace (@.working, '\b', '<b>')
set @.working = replace (@.working, '\i0', '</i>')
set @.working = replace (@.working, '\i', '<i>')
set @.working = replace (@.working, 'SIGNED
\f1''85''85''85''85''85''85''85'
'85''85''85''85''85''85''85''
85''85', '')
set @.working = replace (@.working,
'DATE''85''85''85''85''85''85''85
''85''85''85''85''85''85''85\
''85''85''85', '')
set @.working = replace (@.working, '\f0', '')
set @.working = replace (@.working, '\f1', '')
set @.working = replace (@.working, '\fs20', '')
set @.working = replace (@.working, '\fs16', '')
set @.working = replace (@.working, char (10), '')
set @.working = replace (@.working, char (13), '')
set @.working = replace (@.working, '\tab', '')
set @.working = replace (@.working, '\super', '')
set @.working = replace (@.working, '\nosupersub', '')
set @.working = replace (@.working, '[WAITPHOTO]', '')
set @.working = replace (@.working, '''a3', '￡')
set @.working = replace (@.working, '\par', '<BR>')
set @.working = replace (@.working, '[[]XFO=DN]', '')
set @.working = replace (@.working, '[[]XFO=BN]', '')
set @.working = replace (@.working, '[[]XFO=CN]', '')
set @.working = replace (@.working, '[[]XFO=BM]', '')
set @.working = replace (@.working, '[[]XFO=CM]', '')
return @.working
end

> Are you using LIKE anywhere ...?
I then do a
ALTER view propertiesProperty
as
select reference, dbo. convertPropertyTitle(PropertyAccommodati
on)
PropertyAccommodation, dbo. convertPropertyTitle(propertyfulldetails
)
propertyfulldetails, dbo.convertPropertyTitle(propertysituation)
propertysituation, dbo.convertPropertyTitle(propertytitle) propertytitle
from properties
then reference it as
select * from propertiesproperty
where reference = 'xxx3333'
So no likes anywhere.
Thanks,
Ian

> From Books Online
> Symbol
> Meaning
> LIKE '[ [ ]' [
>
> --
> HTH. Ryan
> "Ian Jagger" <IanJagger@.discussions.microsoft.com> wrote in message
> news:C3850ACE-88F8-437E-94C5-D5FCD83DAEE9@.microsoft.com...
>
>

Replacing / with space

Hi,
I have a column with the following data.
A/B
X Y
Z
X/Y A/D
I want to replace the /with space so that I have the output-
A B
X Y
Z
X Y A D
Can I do that? Please let me know what SQL query will help.
Thanksreplace(column_name, '/', ' ')|||Or SHIFT-F1 will do the trick...|||Thanks a lot. Works.
Originally posted by joejcheng
replace(column_name, '/', ' ')|||No takers for your advice .. blindman

btw .. i tried shift + f1 .. but it didnt replace the / with ' ' ;)sql

Replace-type function for Text datatype

I have a table that has a Text datatype column that has gotten some
garbage
characters in it somehow, probably from key entry. I need to remove
the garbage, multiple occurances of char(15). The replace function
does not work on Text datatype. Any suggestions?Zack Sessions (zcsessions@.visionair.com) writes:
> I have a table that has a Text datatype column that has gotten some
> garbage
> characters in it somehow, probably from key entry. I need to remove
> the garbage, multiple occurances of char(15). The replace function
> does not work on Text datatype. Any suggestions?

One way would be to iterate over the table, and for each row get slices
of 8000 chars to a varchar value on which you run replace(). You would
then use updatetext to update the row. A bit tricky, because if first
got chars 1 to 8000, and removed 6 char(15), you should now start on
char 7994 for the next batch.

Not particularly funny, I know.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hello:

You could write a small vbscript that would loop thru the table and
update the text columns using ado's appendchunk method and the replace
function in vbscript.

See link below on an example that you can adapt to vbscript and your
problem:

http://msdn.microsoft.com/library/d...ples_vb01_8.asp

HTH,

BZ

zcsessions@.visionair.com (Zack Sessions) wrote in message news:<db13d9fb.0308251131.3bb5360d@.posting.google.com>...
> I have a table that has a Text datatype column that has gotten some
> garbage
> characters in it somehow, probably from key entry. I need to remove
> the garbage, multiple occurances of char(15). The replace function
> does not work on Text datatype. Any suggestions?|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93E2DFB86DD18Yazorman@.127.0.0.1>...
> Zack Sessions (zcsessions@.visionair.com) writes:
> > I have a table that has a Text datatype column that has gotten some
> > garbage
> > characters in it somehow, probably from key entry. I need to remove
> > the garbage, multiple occurances of char(15). The replace function
> > does not work on Text datatype. Any suggestions?
> One way would be to iterate over the table, and for each row get slices
> of 8000 chars to a varchar value on which you run replace(). You would
> then use updatetext to update the row. A bit tricky, because if first
> got chars 1 to 8000, and removed 6 char(15), you should now start on
> char 7994 for the next batch.
> Not particularly funny, I know.

Thanks for your response.

I actually thought of trying to do it this way and started to write
the code, but I got stuck on how to get the 8000 character chunks. The
way I read the READTEXT description, it does not return the value into
a local variable. I know how to get the first 8000 characters into a
local varchar, but I haven't figured out how to get any remaining 8000
character chunks. Care to give me a little more help?

Replace values with a "*"

Hi, i have a report which contains a table with several columns. The
first column is filled with some data from a database, and can contain
data, that is some cells can be empty, some can have values...
So my question is, how can i make all the cells in that column that
HAVE a value, to have the value of "*".
Col1 Col1
-- --
1 *
5 HAS TO BE
8 *
4 *
Thnx!Use an IIF built-in function in an expression for the other columns textbox.
David
"ApeX" <jkdmaster_5@.hotmail.com> wrote in message
news:1186655248.283959.105240@.l70g2000hse.googlegroups.com...
> Hi, i have a report which contains a table with several columns. The
> first column is filled with some data from a database, and can contain
> data, that is some cells can be empty, some can have values...
> So my question is, how can i make all the cells in that column that
> HAVE a value, to have the value of "*".
> Col1 Col1
> -- --
> 1 *
> 5 HAS TO BE
> 8 *
> 4 *
> Thnx!
>|||put this in col2 field
=Iif(fields!Col1.value<>'', '*', fields!col1)
"ApeX" wrote:
> Hi, i have a report which contains a table with several columns. The
> first column is filled with some data from a database, and can contain
> data, that is some cells can be empty, some can have values...
> So my question is, how can i make all the cells in that column that
> HAVE a value, to have the value of "*".
> Col1 Col1
> -- --
> 1 *
> 5 HAS TO BE
> 8 *
> 4 *
> Thnx!
>

Replace syntax error

Hello

In my database table I have replaced <h3> Some Text </h3> with <h2> Some Text </h2> in the Description column as below:

UPDATE CAT_Products

SET Description = replace (Description, '</h3>', '</h2>')
WHERE Description <> '</h2>'

UPDATE CAT_Products
SET Description = replace (Description, '<h3>', '<h2>')
WHERE Description <> '<h2>'

However, when I try the same replace syntax code with the DescriptionHTML column in the same table, it does not work, e.g.

UPDATE CAT_Products
SET DescriptionHTML = replace (DescriptionHTML, ' <h3> ', '<h2>')
WHERE Description <> '<h2>'

What do I need to adjust?

Thanks

can you explain what you mean by not working? also pls provide some sample data you have that you are expecting the REPLACE to happen on.

|||

Hello ndinaker

When I put in this code:

UPDATE CAT_Products
SET DescriptionHTML = replace (DescriptionHTML, ' <h3> ', '<h2>')
WHERE Description <> '<h2>'

I dose not execute and I get this error message instead:

[Error] Script lines: 1-5 --------
Argument data type ntext is invalid for argument 1 of replace function.

[Executed: 12/04/07 19:17:21 BST ] [Execution: 0/ms]

As in the text Description column, I am trying to update the <h3> and </h3> to <h2> and </h2> for example: <h3> Some Text </h3> to be replaced by <h2> Some Text </h2>

However, as it is written in html format in the DescriptionHTML database column it looks like:

<h3> Some Text </h 3> to be replaced by <h 2> Some Text </h2>

Thanks

|||

I think the pattern you are matching is only for <h3> and not </h3>. So you probably need to a double replace.

declare

@.str varchar(100)

set

@.str='<h3> Some Text </h3>'

--final value = <h 2> Some Text </h2>

select

@.str,replace(@.str,'<h3>','<h2>'),replace(replace(@.str,'<h3>','<h2>'),'</h3>','</h2>')

|||

Hi ndinakar

Thanks for the code, but I needed to change:

@.str varchar(100)

To

@.str ntext

H

owever this change cause the code to fail:

UPDATE CAT_Products

@.str ntext
set

@.str = '<h3> * </h3>'
--final value = <h 2> * </h2>

select

@.str, replace(@.str,'<h3>', '<h2>'), replace( replace(@.str,'<h3>', '<h2>'), '</h3>', '</h2>')

I got this error message:

[Error] Script lines: 1-13 --------
Line 4: Incorrect syntax near'@.str'.

More exceptions ... Must declare the variable'@.str'.

[Executed: 12/04/07 21:49:22 BST ] [Execution: 0/ms]

|||

Try if this works:

UPDATE

CAT_Products

set

DescriptionHTML=replace(replace(DescriptionHTML,'<h3>','<h2>'),'</h3>','</h2>')

WHERE

Description<>'<h2>'

|||

Before you try the update try a SELECT first to see what records are getting affected and how.

SELECT

NewDescriptionHTML=replace(replace(DescriptionHTML,'<h3>','<h2>'),'</h3>','</h2>'),

DescriptionHTML

FROM

CAT_Products

WHERE

Description<>'<h2>'

|||

As suggested, I tried this first:

SELECT

NewDescriptionHTML = replace( replace(DescriptionHTML,'<h3>', '<h2>'), '</h3>', '</h2>'),
DescriptionHTML

FROM

CAT_Products
WHERE

DescriptionHTML <> '<h2>'

But I got this error message:

[Error] Script lines: 1-11 --------
Argument data type ntext is invalid for argument 1 of replace function.

More exceptions ... The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[Executed: 12/04/07 23:40:42 BST ] [Execution: 0/ms]

Then I tried this:

UPDATE

CAT_Products
set

DescriptionHTML = replace( replace(DescriptionHTML,'<h3>', '<h2>'), '</h3>', '</h2>')
WHERE

DescriptionHTML <> '<h2>'

But I got this error message:

[Error] Script lines: 1-11 --------
Argument data type ntext is invalid for argument 1 of replace function.

More exceptions ... The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[Executed: 12/04/07 23:43:54 BST ] [Execution: 0/ms]

It looks like I will have to update this column manually, I had best roll up my sleeves.

Thanks

|||Ahhhh...its the NTEXT. Its going to be a pain to retrieve those values/update them through the query analyzer. Perhaps you can write a little tool..a VB tool with a form...that retrieves the values into a textbox and a button to update it..that will be easier..|||

Try this:

SELECT

NewDescriptionHTML

=replace(replace(convert(varchar(4000),DescriptionHTML),'<h3>','<h2>'),'</h3>','</h2>'),

DescriptionHTML

FROM

CAT_Products

WHERE

Description<>'<h2>'

|||

Hi ndinakar

Thanks again for your help and support

Yes you are right the Ntext is a pain, cos when I tried your latest code suggestion I got this error:

[Error] Script lines: 1-15 --------
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[Executed: 13/04/07 09:17:43 BST ] [Execution: 0/ms]

It certainly dose not like the NTEXT.

A cup of coffee and rolling up of the sleeves coming up.

Thanks

sql

Replace strings in Text column

Hi!
I would like to replace some strings (for instance 'mystring1' with 'mystring2') in a column of datatype Text. Replace function does not work with Text columns. The following works:
update mytable set myfield=replace(convert(varchar(8000), myfield),'mystring1','mystring2')
but it truncates data the exceed the 8000 bytes. Ofcourse I have some rows containing more than 8000 bytes in that field, that's why it is set a Text.
Any ideas?You might be able to use PATINDEX along with UPDATETEXT to replace all occurances in your TEXT column. Have a look here:

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx
http://www.aspfaq.com/show.asp?id=2445

However, I think it is more effective to do such things client-side.
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)
|||Thanks Frank. This will do the job.sql

Replace question...

I have a text column with some text that needs to be removed.
Here is an example:

body_text = 'Title=Title
Subtitle=Subtitle
Subtitle2=subtitle2

story content here, more content
more content'

is there a way to strip out the title=title,etc. up to the beginning of the story content?

- Thanks...Can you explain what you whant a little more.

______________
Paulo Gonalves|||Sure.

I have a field in my table that contains an article. These articles were converted over from another system, so when I converted them I appended the article's title, subtitle, subtitle2, and subtitle3 to the beginning of the article so the editors could have access to that information. The conversion and cleanup process have been completed and I now need to strip the title/subtitle information OUT of the article while leaving the article text intact. Is this possible?|||If i understand write what you need is to clean the information in two fields of each record.
If it's this you just use the Update instruction to update those fileds with an empty value.

______________
Paulo Gonalves|||The problem is that ALL the information is in one field. The title/subtitle/subtitle2/subtitle3 AND article content are all in one field. Is it possible to clean the title/subtitle information while leave the article content intact?|||Ok, in that case first Slect the record to be cleaned pass the entire row to a variable and then cut the information from position the first charecter (0) until the secont / (if they are separated by /).
But the best thing you could do it's import that information to a new table, creating a colune for each field.

______________
Paulo Gonalves|||I don't know if I have your exact scenario in mind but maybe this will help:

-- setup a table
create table #tmp(RecordID int not null identity(1,1), Article text)

-- populate the table with some data
insert into #tmp (Article)
values(
'body_text = ''Title=Title
Subtitle=Subtitle
Subtitle2=subtitle2

story content here, more content
more content' )

insert into #tmp (Article)
values(
'body_text = ''Title=Title
Subtitle=Subtitle
Subtitle2=subtitle2

Big story content here, more content
more content' + char(13) + char(10) + replicate('*&',3800) )

-- select the two rows just entered, I did it this way to help seperate the text.
select cast(Article as varchar(100)) From #Tmp where RecordID = 1
select cast(Article as varchar(100)) From #Tmp where RecordID = 2

-- Declare two variable, One to hold the characters used to define "NewLine"
-- and one to hold the patter we will be looking for
declare @.Pattern varchar(100), @.NewLine varchar(2)
set @.NewLine = char(13) + char(10)
set @.Pattern = 'body_text%' + @.NewLine + @.NewLine + '%'

-- Essentially flip the text and pattern backwards and look for the first occurence
-- of pattern.
select RecordID
, patindex(reverse(@.Pattern),reverse(cast(Article as varchar(8000)))) as 'Right most ende of test to strip'
, datalength(Article) as 'Text length'
, Substring(Article,datalength(Article) - patindex(reverse(@.Pattern),reverse(cast(Article as varchar(8000)))) + 2, 8000)
from #Tmp
where patindex(reverse(@.Pattern),reverse(cast(Article as varchar(8000)))) > 0
drop table #tmp

Of course this us untested for your environment. You will need to evaluate this to see if it meets your needs.

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 password with "******"

How can I replace password in SQL server table column with ******
In Access it can be done by Input mask: password
How can I do it in SQL server?
Thanks for help!
Simon
There is no such thing as an input mask in SQL Server.
What you could do is create a view that converts the password to ********,
and grant access for your users only to the view and not to the underlying
table.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Simon Abolnar" <Simon.Abolnar@.tscng.net> wrote in message
news:uimePG#KFHA.1156@.TK2MSFTNGP09.phx.gbl...
> How can I replace password in SQL server table column with ******
> In Access it can be done by Input mask: password
> How can I do it in SQL server?
> Thanks for help!
> Simon
>
|||Access is both a front end and backend tool (questionable about how good
it is, but that's a different discussion).
SQL Server won't visually present data any differently than it is stored
(outside of font choice).
If you want stars to appear, that is something you will have to program
around in your front end application, not the database system.
Simon Worth
Simon Abolnar wrote:
> How can I replace password in SQL server table column with ******
> In Access it can be done by Input mask: password
> How can I do it in SQL server?
> Thanks for help!
> Simon
>
|||I threw together an article on this. No, not just for you; this is a common
enough question. :-)
http://www.aspfaq.com/2536
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Simon Abolnar" <Simon.Abolnar@.tscng.net> wrote in message
news:uimePG#KFHA.1156@.TK2MSFTNGP09.phx.gbl...
> How can I replace password in SQL server table column with ******
> In Access it can be done by Input mask: password
> How can I do it in SQL server?
> Thanks for help!
> Simon
>

Replace password with "******"

How can I replace password in SQL server table column with ******
In Access it can be done by Input mask: password
How can I do it in SQL server?
Thanks for help!
SimonThere is no such thing as an input mask in SQL Server.
What you could do is create a view that converts the password to ********,
and grant access for your users only to the view and not to the underlying
table.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Simon Abolnar" <Simon.Abolnar@.tscng.net> wrote in message
news:uimePG#KFHA.1156@.TK2MSFTNGP09.phx.gbl...
> How can I replace password in SQL server table column with ******
> In Access it can be done by Input mask: password
> How can I do it in SQL server?
> Thanks for help!
> Simon
>|||Access is both a front end and backend tool (questionable about how good
it is, but that's a different discussion).
SQL Server won't visually present data any differently than it is stored
(outside of font choice).
If you want stars to appear, that is something you will have to program
around in your front end application, not the database system.
Simon Worth
Simon Abolnar wrote:
> How can I replace password in SQL server table column with ******
> In Access it can be done by Input mask: password
> How can I do it in SQL server?
> Thanks for help!
> Simon
>|||I threw together an article on this. No, not just for you; this is a common
enough question. :-)
http://www.aspfaq.com/2536
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Simon Abolnar" <Simon.Abolnar@.tscng.net> wrote in message
news:uimePG#KFHA.1156@.TK2MSFTNGP09.phx.gbl...
> How can I replace password in SQL server table column with ******
> In Access it can be done by Input mask: password
> How can I do it in SQL server?
> Thanks for help!
> Simon
>

Replace password with "******"

How can I replace password in SQL server table column with ******
In Access it can be done by Input mask: password
How can I do it in SQL server?
Thanks for help!
SimonThere is no such thing as an input mask in SQL Server.
What you could do is create a view that converts the password to ********,
and grant access for your users only to the view and not to the underlying
table.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Simon Abolnar" <Simon.Abolnar@.tscng.net> wrote in message
news:uimePG#KFHA.1156@.TK2MSFTNGP09.phx.gbl...
> How can I replace password in SQL server table column with ******
> In Access it can be done by Input mask: password
> How can I do it in SQL server?
> Thanks for help!
> Simon
>|||Access is both a front end and backend tool (questionable about how good
it is, but that's a different discussion).
SQL Server won't visually present data any differently than it is stored
(outside of font choice).
If you want stars to appear, that is something you will have to program
around in your front end application, not the database system.
Simon Worth
Simon Abolnar wrote:
> How can I replace password in SQL server table column with ******
> In Access it can be done by Input mask: password
> How can I do it in SQL server?
> Thanks for help!
> Simon
>|||I threw together an article on this. No, not just for you; this is a common
enough question. :-)
http://www.aspfaq.com/2536
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Simon Abolnar" <Simon.Abolnar@.tscng.net> wrote in message
news:uimePG#KFHA.1156@.TK2MSFTNGP09.phx.gbl...
> How can I replace password in SQL server table column with ******
> In Access it can be done by Input mask: password
> How can I do it in SQL server?
> Thanks for help!
> Simon
>