Replacing NULL characters from within a flat file with script?

Here's one for the group:

I have a fixed-width flat file. Within some of the rows, I have embedded NULL characters. The inherit problem is that NULL characters are string terminators, so using a flat file source doesn't allow the capturing of these NULL characters or any characters after the first NULL character -- only the string up to the NULL character.

So, within SSIS, what would be the best way to replace NULL characters with a SPACE character? My file is fixed-width, and replacing with a space will allow me to keep the length the same. I am not opposed to running a script task against the file first (before using my flat file source), but would need some guidance as I'm not a .Net guru, by any means.

Unfortunately, going to the bank to have them correct this file has proved fruitless. We're going to have to deal with these characters on our side.

Thanks,
Phil

Just curious, what does a null character look like in a flat file? I've encountered parsing problems in some basic BAI bank files that were comma delimited. Basically, I couldn't bulk insert or bcp the data in through SQL scripting, but had to use ActiveX to import the data or the SQL 2000 (probably 2005 also) DTS Text Connection Object (because somehow DTS ignored the problem characters and found the end-of-line return like it is supposed to). The basic run-down on using ADO/ActiveX can be found here: http://msdn2.microsoft.com/en-us/library/ms974559.aspx.

Anyway, if you come across another solution please let me know.

Good luck!

|||A NULL character is a hex 00.|||Bump|||

Hi Phil,

I would write a tiny C program to do the conversion and then call it from the SSIS package.

BTW, what is the semantic of these embedded nulls in string fields, are they some kind of dividers?

Thanks,

Bob

sql

Replacing NULL characters from within a flat file

Here's one for the group:

I have a fixed-width flat file. Within some of the rows, I have embedded NULL characters. The inherit problem is that NULL characters are string terminators, so using a flat file source doesn't allow the capturing of these NULL characters or any characters after the first NULL character -- only the string up to the NULL character.

So, within SSIS, what would be the best way to replace NULL characters with a SPACE character? My file is fixed-width, and replacing with a space will allow me to keep the length the same. I am not opposed to running a script task against the file first (before using my flat file source), but would need some guidance as I'm not a .Net guru, by any means.

Unfortunately, going to the bank to have them correct this file has proved fruitless. We're going to have to deal with these characters on our side.

Thanks,
Phil

Just curious, what does a null character look like in a flat file? I've encountered parsing problems in some basic BAI bank files that were comma delimited. Basically, I couldn't bulk insert or bcp the data in through SQL scripting, but had to use ActiveX to import the data or the SQL 2000 (probably 2005 also) DTS Text Connection Object (because somehow DTS ignored the problem characters and found the end-of-line return like it is supposed to). The basic run-down on using ADO/ActiveX can be found here: http://msdn2.microsoft.com/en-us/library/ms974559.aspx.

Anyway, if you come across another solution please let me know.

Good luck!

|||A NULL character is a hex 00.|||Bump|||

Hi Phil,

I would write a tiny C program to do the conversion and then call it from the SSIS package.

BTW, what is the semantic of these embedded nulls in string fields, are they some kind of dividers?

Thanks,

Bob

|||Have also encountered this problem and wondered if anyone has written any code to change the ASCII NULL character to a SPACE or another harmless printable character ?

Replacing characters in SQL

I am incorporating a perl script loading data into my SQL Server. If I receive a message with a single backslash \ I know to replace it with a double backslash \\. But what if it is a " double quote what do I need to do to get it to appear as is?
ThanksYou'll love this answer: It depends.

Based on your previous message, you might have chosen to change the settings of QUOTED_IDENTIFIER. If you have changed it, then from Perl there is no way to pass a true double quote anymore. If you have not changed it, you don't need to do anything at all to a double quote.

My suggestion would be to leave the quoted identifier off, then use a simple s/'/''/gto fix up any apostrophes in the strings comming from Perl to SQL Server.

-PatP|||Thanks for you help.

-Laurasql

Replacing characters in a text field

I have a large table, tblMessage, which stores e-mail messages in text
fields. I need to remove the carriage returns the data in these fields,
but I have not yet figured out how to do so.

I thought that the way to do this would be with the REPLACE function;
unfortunately, of course, the REPLACE function cannot work with TEXT
fields. I tried CASTing the text field to VARCHAR(8000); however, some
of the rows have more than 8000 characters in the text field, so it bombs.

Here is the SQL that I tried:

select
msgID,
msgSent,
msgFromType,
msgFromID,
msgSubject,
REPLACE (CAST(msgMessage AS varchar(8000)), CHAR(13), '<BR>') AS
newMessage,
msgOriginal,
attID
into tblMessageNew
from tblMessage

I'm at my wit's end. Truncating the text field to 8000 character is an
acceptable option, but I can't even seem to be able to do that.

I'm using SQL Server version 7.Richard S. Crawford (rscrawfordDUCK@.mossREMOVEWATERFOWLroot.com) writes:
> I have a large table, tblMessage, which stores e-mail messages in text
> fields. I need to remove the carriage returns the data in these fields,
> but I have not yet figured out how to do so.
> I thought that the way to do this would be with the REPLACE function;
> unfortunately, of course, the REPLACE function cannot work with TEXT
> fields. I tried CASTing the text field to VARCHAR(8000); however, some
> of the rows have more than 8000 characters in the text field, so it bombs.

Bombs with what? It's always helpful if you include the error message.

I was able to run this on SQL Server 7:

create table hh (a text not null)
go
declare @.d varchar(8000), @.df varchar(8000)
select @.d = replicate('Why are you here? You should be there!', 8000/30)
select @.df = replicate('Why are you here? You should be there!', 8000/30)
insert hh (a)
exec ('select ''' + @.d + @.df + '''')
select datalength(a) from hh
select replace(cast(a as varchar(8000)), 'Why', 'Porque') from hh
go
drop table hh

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

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

Taking the CR/LF out before inserting the text would avoid your problem!!!

The only way I can think of doing this is to chunk the text field into 8000
character as in
http://tinyurl.com/3fqxv

One way of doing this:
http://tinyurl.com/236hf

John

"Richard S. Crawford" <rscrawfordDUCK@.mossREMOVEWATERFOWLroot.com> wrote in
message news:ca4rog$d8b$1@.woodrow.ucdavis.edu...
> I have a large table, tblMessage, which stores e-mail messages in text
> fields. I need to remove the carriage returns the data in these fields,
> but I have not yet figured out how to do so.
> I thought that the way to do this would be with the REPLACE function;
> unfortunately, of course, the REPLACE function cannot work with TEXT
> fields. I tried CASTing the text field to VARCHAR(8000); however, some
> of the rows have more than 8000 characters in the text field, so it bombs.
> Here is the SQL that I tried:
> select
> msgID,
> msgSent,
> msgFromType,
> msgFromID,
> msgSubject,
> REPLACE (CAST(msgMessage AS varchar(8000)), CHAR(13), '<BR>') AS
> newMessage,
> msgOriginal,
> attID
> into tblMessageNew
> from tblMessage
> I'm at my wit's end. Truncating the text field to 8000 character is an
> acceptable option, but I can't even seem to be able to do that.
> I'm using SQL Server version 7.

Replacements of substrings in strings

This is not a piece of cake as I thought.

Have to replace few characters with few other characters in the string.

Now, I am using nested Replace:

(Replace(Replace(MyString,'','UE'),'','OE')

This example is simplified, number of replacements is more than eight of them.

Is there any other, more elegant, way to do this in just one command?No, but if you find yourself doing the same REPLACE in several places in your code, you can roll it into a function for ease of programming and clarity.|||Please ealborate a little...first read the sticky at the top of the forum...but my guess is you need to fix a table in one shot...

I would write a view, unload it, then reload the table...

Just a guess though

I hate DB2 OS/390

Replacement of values within a string

Hi,
I would like to find out what function I would need to
call to replace characters other than asterisks (*) in a
string value.
For example, I have values in a column in a table like
********SFWE, *****100****, *****200****, *****300****,
and *****400****. I now want these values to be
represented as ********YYYY, *****YYY****, *****YYY****,
*****YYY****, respectively. I want all non-asterisks
value in the string to be replaced with 'Y'.
Thank you in advance,
bpdeeTake a look at CharIndex...
Rick
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
> Hi,
> I would like to find out what function I would need to
> call to replace characters other than asterisks (*) in a
> string value.
> For example, I have values in a column in a table like
> ********SFWE, *****100****, *****200****, *****300****,
> and *****400****. I now want these values to be
> represented as ********YYYY, *****YYY****, *****YYY****,
> *****YYY****, respectively. I want all non-asterisks
> value in the string to be replaced with 'Y'.
> Thank you in advance,
> bpdee|||Hi Rick,
Thank you for your response. Although, I would not know
other than it is a non-asterisk value that is in the
string that I need to replace with the value of 'Y'. It
could be any alphanumeric value and any combination of it
that is in the string that I need to replace. It could
also be anywhere in the string.
Thanks,
bpdee
>--Original Message--
>Take a look at CharIndex...
>Rick
>"bpdee" <anonymous@.discussions.microsoft.com> wrote in
message
>news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
>> Hi,
>> I would like to find out what function I would need to
>> call to replace characters other than asterisks (*) in a
>> string value.
>> For example, I have values in a column in a table like
>> ********SFWE, *****100****, *****200****, *****300****,
>> and *****400****. I now want these values to be
>> represented as ********YYYY, *****YYY****, *****YYY****,
>> *****YYY****, respectively. I want all non-asterisks
>> value in the string to be replaced with 'Y'.
>> Thank you in advance,
>> bpdee
>
>.
>|||You probably want to do something like this. I've got the first part of the
replace working, you can probably figure out the second part. You might
want to put this into a function...
The last column in the example is what you're after; the others are so you
can follow what I'm doing.
declare @.myfield varchar(100)
set @.myfield = '*****100****'
select CHARINDEX('*', @.myfield),
PATINDEX('%[^*]%', @.myfield)-1,
RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1), -- you might want to
put 100 Y's into the string at the start - depending on what you expect to
have in your field
STUFF(@.myfield, CHARINDEX('*', @.myfield), PATINDEX('%[^*]%', @.myfield)-1,
RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1))
Andre
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
> Hi,
> I would like to find out what function I would need to
> call to replace characters other than asterisks (*) in a
> string value.
> For example, I have values in a column in a table like
> ********SFWE, *****100****, *****200****, *****300****,
> and *****400****. I now want these values to be
> represented as ********YYYY, *****YYY****, *****YYY****,
> *****YYY****, respectively. I want all non-asterisks
> value in the string to be replaced with 'Y'.
> Thank you in advance,
> bpdee|||Hi Andre,
Thank you for your response. You are correct that the
second one is the one I need to solve my problem. I ran
the select statement against my database and it is
definitely closer to what I need. Although, the result is
actually the opposite of what I want. The result I got
is 'YYYYYYYYSFWE' while I wanted is '********YYYY'. I
want to keep all asterisks and replace those in the string
value that is NOT an asterisk with 'Y'. How would I do
that using the select you sent me?
Thanks,
bpdee
>--Original Message--
>You probably want to do something like this. I've got
the first part of the
>replace working, you can probably figure out the second
part. You might
>want to put this into a function...
>The last column in the example is what you're after; the
others are so you
>can follow what I'm doing.
>declare @.myfield varchar(100)
>set @.myfield = '*****100****'
>select CHARINDEX('*', @.myfield),
> PATINDEX('%[^*]%', @.myfield)-1,
> RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1), --
you might want to
>put 100 Y's into the string at the start - depending on
what you expect to
>have in your field
> STUFF(@.myfield, CHARINDEX('*', @.myfield), PATINDEX('%
[^*]%', @.myfield)-1,
>RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1))
>Andre
>
>"bpdee" <anonymous@.discussions.microsoft.com> wrote in
message
>news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
>> Hi,
>> I would like to find out what function I would need to
>> call to replace characters other than asterisks (*) in a
>> string value.
>> For example, I have values in a column in a table like
>> ********SFWE, *****100****, *****200****, *****300****,
>> and *****400****. I now want these values to be
>> represented as ********YYYY, *****YYY****, *****YYY****,
>> *****YYY****, respectively. I want all non-asterisks
>> value in the string to be replaced with 'Y'.
>> Thank you in advance,
>> bpdee
>
>.
>|||Hi,
You can probably use ASCII function to determine the ASCII value of *...
CREATE TABLE #temp
(a1 int, a2 char(1), a3 char(1))
DECLARE @.position int, @.string char(12)
SET @.position = 1
SET @.string = '********SFWE'
WHILE @.position <= DATALENGTH(@.string)
BEGIN
INSERT INTO #temp SELECT ASCII(SUBSTRING(@.string, @.position, 1)) a1,
CHAR(ASCII(SUBSTRING(@.string, @.position, 1))) a2, 'Y' a3
SET @.position = @.position + 1
END
SELECT a2,a3 FROM #temp
WHERE a1 NOT IN( 42, 44, 32)
Now I guess you can figure out, how you can replace a2 with a3 and transform
them into columns...
Thanks
GYK
"bpdee" wrote:
> Hi Rick,
> Thank you for your response. Although, I would not know
> other than it is a non-asterisk value that is in the
> string that I need to replace with the value of 'Y'. It
> could be any alphanumeric value and any combination of it
> that is in the string that I need to replace. It could
> also be anywhere in the string.
> Thanks,
> bpdee
>
> >--Original Message--
> >Take a look at CharIndex...
> >
> >Rick
> >
> >"bpdee" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
> >> Hi,
> >>
> >> I would like to find out what function I would need to
> >> call to replace characters other than asterisks (*) in a
> >> string value.
> >>
> >> For example, I have values in a column in a table like
> >> ********SFWE, *****100****, *****200****, *****300****,
> >> and *****400****. I now want these values to be
> >> represented as ********YYYY, *****YYY****, *****YYY****,
> >> *****YYY****, respectively. I want all non-asterisks
> >> value in the string to be replaced with 'Y'.
> >>
> >> Thank you in advance,
> >> bpdee
> >
> >
> >.
> >
>|||Here is some code that should work...
You could make this into a sproc or a function...
DECLARE @.OrgCol varchar(100)
@.Count int,
@.NewCol varchar(100)
SELECT @.OrgCol = ColumnName FROM TableName
SET @.Count = 1
SET @.NewCol = ''
WHILE (@.Count < DATALENGTH(@.OrgCol))
BEGIN
IF (SUBSTRING(@.OrgCol, @.Count, 1) <> '*')
SET @.NewCol = @.NewCol + 'Y'
ELSE
SET @.NewCol = @.NewCol + SUBSTRING(@.OrgCol, @.Count, 1)
SET @.Count = @.Count + 1
END
-- Write your update statement here or return @.NewCol if a UDF
Rick Sawtell
MCT, MCSD, MCDBA|||Sorry, I didn't read well enough. :)
While I still think functions are efficient and might be a good thing to
use, if I can accomplish what I want in a query, I'll generally pick that
route first. That said, give this a whirl. It worked for me regardless of
whether or not there were asterisks at the beginning or end of the string,
and no matter how many there were. The reason I'd opt for a function is
this isn't very intuitive to understand when you look at it, and you can
probably take a more intuitive approach in a function, like the examples
that have been given by others here.
declare @.myfield varchar(100)
set @.myfield = '******SFWE'
select STUFF(@.myfield, PATINDEX('%[^*]%', @.myfield), 4, RIGHT('YYYYYYYYYY',
(LEN(@.myfield) - ((PATINDEX('%[^*]%', @.myfield)-1) + (PATINDEX('%[^*]%',
REVERSE(@.myfield))-1)))))
I hope this helps.
Andre
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:155701c4b627$8638b6a0$a501280a@.phx.gbl...
> Hi Andre,
> Thank you for your response. You are correct that the
> second one is the one I need to solve my problem. I ran
> the select statement against my database and it is
> definitely closer to what I need. Although, the result is
> actually the opposite of what I want. The result I got
> is 'YYYYYYYYSFWE' while I wanted is '********YYYY'. I
> want to keep all asterisks and replace those in the string
> value that is NOT an asterisk with 'Y'. How would I do
> that using the select you sent me?
> Thanks,
> bpdee
>>--Original Message--
>>You probably want to do something like this. I've got
> the first part of the
>>replace working, you can probably figure out the second
> part. You might
>>want to put this into a function...
>>The last column in the example is what you're after; the
> others are so you
>>can follow what I'm doing.
>>declare @.myfield varchar(100)
>>set @.myfield = '*****100****'
>>select CHARINDEX('*', @.myfield),
>> PATINDEX('%[^*]%', @.myfield)-1,
>> RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1), --
> you might want to
>>put 100 Y's into the string at the start - depending on
> what you expect to
>>have in your field
>> STUFF(@.myfield, CHARINDEX('*', @.myfield), PATINDEX('%
> [^*]%', @.myfield)-1,
>>RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1))
>>Andre
>>
>>"bpdee" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
>> Hi,
>> I would like to find out what function I would need to
>> call to replace characters other than asterisks (*) in a
>> string value.
>> For example, I have values in a column in a table like
>> ********SFWE, *****100****, *****200****, *****300****,
>> and *****400****. I now want these values to be
>> represented as ********YYYY, *****YYY****, *****YYY****,
>> *****YYY****, respectively. I want all non-asterisks
>> value in the string to be replaced with 'Y'.
>> Thank you in advance,
>> bpdee
>>
>>.|||Hi Rick,
Thank you so much, Rick! This did the trick.
Thanks again,
Bettina
"Rick Sawtell" wrote:
> Here is some code that should work...
> You could make this into a sproc or a function...
> DECLARE @.OrgCol varchar(100)
> @.Count int,
> @.NewCol varchar(100)
> SELECT @.OrgCol = ColumnName FROM TableName
> SET @.Count = 1
> SET @.NewCol = ''
> WHILE (@.Count < DATALENGTH(@.OrgCol))
> BEGIN
> IF (SUBSTRING(@.OrgCol, @.Count, 1) <> '*')
> SET @.NewCol = @.NewCol + 'Y'
> ELSE
> SET @.NewCol = @.NewCol + SUBSTRING(@.OrgCol, @.Count, 1)
> SET @.Count = @.Count + 1
> END
> -- Write your update statement here or return @.NewCol if a UDF
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Replacement of values within a string

Hi,
I would like to find out what function I would need to
call to replace characters other than asterisks (*) in a
string value.
For example, I have values in a column in a table like
********SFWE, *****100****, *****200****, *****300****,
and *****400****. I now want these values to be
represented as ********YYYY, *****YYY****, *****YYY****,
*****YYY****, respectively. I want all non-asterisks
value in the string to be replaced with 'Y'.
Thank you in advance,
bpdee
Take a look at CharIndex...
Rick
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
> Hi,
> I would like to find out what function I would need to
> call to replace characters other than asterisks (*) in a
> string value.
> For example, I have values in a column in a table like
> ********SFWE, *****100****, *****200****, *****300****,
> and *****400****. I now want these values to be
> represented as ********YYYY, *****YYY****, *****YYY****,
> *****YYY****, respectively. I want all non-asterisks
> value in the string to be replaced with 'Y'.
> Thank you in advance,
> bpdee
|||Hi Rick,
Thank you for your response. Although, I would not know
other than it is a non-asterisk value that is in the
string that I need to replace with the value of 'Y'. It
could be any alphanumeric value and any combination of it
that is in the string that I need to replace. It could
also be anywhere in the string.
Thanks,
bpdee

>--Original Message--
>Take a look at CharIndex...
>Rick
>"bpdee" <anonymous@.discussions.microsoft.com> wrote in
message
>news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
>
>.
>
|||You probably want to do something like this. I've got the first part of the
replace working, you can probably figure out the second part. You might
want to put this into a function...
The last column in the example is what you're after; the others are so you
can follow what I'm doing.
declare @.myfield varchar(100)
set @.myfield = '*****100****'
select CHARINDEX('*', @.myfield),
PATINDEX('%[^*]%', @.myfield)-1,
RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1), -- you might want to
put 100 Y's into the string at the start - depending on what you expect to
have in your field
STUFF(@.myfield, CHARINDEX('*', @.myfield), PATINDEX('%[^*]%', @.myfield)-1,
RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1))
Andre
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
> Hi,
> I would like to find out what function I would need to
> call to replace characters other than asterisks (*) in a
> string value.
> For example, I have values in a column in a table like
> ********SFWE, *****100****, *****200****, *****300****,
> and *****400****. I now want these values to be
> represented as ********YYYY, *****YYY****, *****YYY****,
> *****YYY****, respectively. I want all non-asterisks
> value in the string to be replaced with 'Y'.
> Thank you in advance,
> bpdee
|||Hi Andre,
Thank you for your response. You are correct that the
second one is the one I need to solve my problem. I ran
the select statement against my database and it is
definitely closer to what I need. Although, the result is
actually the opposite of what I want. The result I got
is 'YYYYYYYYSFWE' while I wanted is '********YYYY'. I
want to keep all asterisks and replace those in the string
value that is NOT an asterisk with 'Y'. How would I do
that using the select you sent me?
Thanks,
bpdee

>--Original Message--
>You probably want to do something like this. I've got
the first part of the
>replace working, you can probably figure out the second
part. You might
>want to put this into a function...
>The last column in the example is what you're after; the
others are so you
>can follow what I'm doing.
>declare @.myfield varchar(100)
>set @.myfield = '*****100****'
>select CHARINDEX('*', @.myfield),
> PATINDEX('%[^*]%', @.myfield)-1,
> RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1), --
you might want to
>put 100 Y's into the string at the start - depending on
what you expect to
>have in your field
> STUFF(@.myfield, CHARINDEX('*', @.myfield), PATINDEX('%
[^*]%', @.myfield)-1,
>RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1))
>Andre
>
>"bpdee" <anonymous@.discussions.microsoft.com> wrote in
message
>news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
>
>.
>
|||Hi,
You can probably use ASCII function to determine the ASCII value of *...
CREATE TABLE #temp
(a1 int, a2 char(1), a3 char(1))
DECLARE @.position int, @.string char(12)
SET @.position = 1
SET @.string = '********SFWE'
WHILE @.position <= DATALENGTH(@.string)
BEGIN
INSERT INTO #temp SELECT ASCII(SUBSTRING(@.string, @.position, 1)) a1,
CHAR(ASCII(SUBSTRING(@.string, @.position, 1))) a2, 'Y' a3
SET @.position = @.position + 1
END
SELECT a2,a3 FROM #temp
WHERE a1 NOT IN( 42, 44, 32)
Now I guess you can figure out, how you can replace a2 with a3 and transform
them into columns...
Thanks
GYK
"bpdee" wrote:

> Hi Rick,
> Thank you for your response. Although, I would not know
> other than it is a non-asterisk value that is in the
> string that I need to replace with the value of 'Y'. It
> could be any alphanumeric value and any combination of it
> that is in the string that I need to replace. It could
> also be anywhere in the string.
> Thanks,
> bpdee
>
> message
>
|||Here is some code that should work...
You could make this into a sproc or a function...
DECLARE @.OrgCol varchar(100)
@.Count int,
@.NewCol varchar(100)
SELECT @.OrgCol = ColumnName FROM TableName
SET @.Count = 1
SET @.NewCol = ''
WHILE (@.Count < DATALENGTH(@.OrgCol))
BEGIN
IF (SUBSTRING(@.OrgCol, @.Count, 1) <> '*')
SET @.NewCol = @.NewCol + 'Y'
ELSE
SET @.NewCol = @.NewCol + SUBSTRING(@.OrgCol, @.Count, 1)
SET @.Count = @.Count + 1
END
-- Write your update statement here or return @.NewCol if a UDF
Rick Sawtell
MCT, MCSD, MCDBA
|||Sorry, I didn't read well enough.
While I still think functions are efficient and might be a good thing to
use, if I can accomplish what I want in a query, I'll generally pick that
route first. That said, give this a whirl. It worked for me regardless of
whether or not there were asterisks at the beginning or end of the string,
and no matter how many there were. The reason I'd opt for a function is
this isn't very intuitive to understand when you look at it, and you can
probably take a more intuitive approach in a function, like the examples
that have been given by others here.
declare @.myfield varchar(100)
set @.myfield = '******SFWE'
select STUFF(@.myfield, PATINDEX('%[^*]%', @.myfield), 4, RIGHT('YYYYYYYYYY',
(LEN(@.myfield) - ((PATINDEX('%[^*]%', @.myfield)-1) + (PATINDEX('%[^*]%',
REVERSE(@.myfield))-1)))))
I hope this helps.
Andre
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:155701c4b627$8638b6a0$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi Andre,
> Thank you for your response. You are correct that the
> second one is the one I need to solve my problem. I ran
> the select statement against my database and it is
> definitely closer to what I need. Although, the result is
> actually the opposite of what I want. The result I got
> is 'YYYYYYYYSFWE' while I wanted is '********YYYY'. I
> want to keep all asterisks and replace those in the string
> value that is NOT an asterisk with 'Y'. How would I do
> that using the select you sent me?
> Thanks,
> bpdee
> the first part of the
> part. You might
> others are so you
> you might want to
> what you expect to
> [^*]%', @.myfield)-1,
> message
|||Hi Rick,
Thank you so much, Rick! This did the trick.
Thanks again,
Bettina
"Rick Sawtell" wrote:

> Here is some code that should work...
> You could make this into a sproc or a function...
> DECLARE @.OrgCol varchar(100)
> @.Count int,
> @.NewCol varchar(100)
> SELECT @.OrgCol = ColumnName FROM TableName
> SET @.Count = 1
> SET @.NewCol = ''
> WHILE (@.Count < DATALENGTH(@.OrgCol))
> BEGIN
> IF (SUBSTRING(@.OrgCol, @.Count, 1) <> '*')
> SET @.NewCol = @.NewCol + 'Y'
> ELSE
> SET @.NewCol = @.NewCol + SUBSTRING(@.OrgCol, @.Count, 1)
> SET @.Count = @.Count + 1
> END
> -- Write your update statement here or return @.NewCol if a UDF
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Replacement of values within a string

Hi,
I would like to find out what function I would need to
call to replace characters other than asterisks (*) in a
string value.
For example, I have values in a column in a table like
********SFWE, *****100****, *****200****, *****300****,
and *****400****. I now want these values to be
represented as ********YYYY, *****YYY****, *****YYY****,
*****YYY****, respectively. I want all non-asterisks
value in the string to be replaced with 'Y'.
Thank you in advance,
bpdeeTake a look at CharIndex...
Rick
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
> Hi,
> I would like to find out what function I would need to
> call to replace characters other than asterisks (*) in a
> string value.
> For example, I have values in a column in a table like
> ********SFWE, *****100****, *****200****, *****300****,
> and *****400****. I now want these values to be
> represented as ********YYYY, *****YYY****, *****YYY****,
> *****YYY****, respectively. I want all non-asterisks
> value in the string to be replaced with 'Y'.
> Thank you in advance,
> bpdee|||Hi Rick,
Thank you for your response. Although, I would not know
other than it is a non-asterisk value that is in the
string that I need to replace with the value of 'Y'. It
could be any alphanumeric value and any combination of it
that is in the string that I need to replace. It could
also be anywhere in the string.
Thanks,
bpdee

>--Original Message--
>Take a look at CharIndex...
>Rick
>"bpdee" <anonymous@.discussions.microsoft.com> wrote in
message
>news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
>
>.
>|||You probably want to do something like this. I've got the first part of the
replace working, you can probably figure out the second part. You might
want to put this into a function...
The last column in the example is what you're after; the others are so you
can follow what I'm doing.
declare @.myfield varchar(100)
set @.myfield = '*****100****'
select CHARINDEX('*', @.myfield),
PATINDEX('%[^*]%', @.myfield)-1,
RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1), -- you might want t
o
put 100 Y's into the string at the start - depending on what you expect to
have in your field
STUFF(@.myfield, CHARINDEX('*', @.myfield), PATINDEX('%[^*]%', @.myfield)-1
,
RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1))
Andre
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
> Hi,
> I would like to find out what function I would need to
> call to replace characters other than asterisks (*) in a
> string value.
> For example, I have values in a column in a table like
> ********SFWE, *****100****, *****200****, *****300****,
> and *****400****. I now want these values to be
> represented as ********YYYY, *****YYY****, *****YYY****,
> *****YYY****, respectively. I want all non-asterisks
> value in the string to be replaced with 'Y'.
> Thank you in advance,
> bpdee|||Hi Andre,
Thank you for your response. You are correct that the
second one is the one I need to solve my problem. I ran
the select statement against my database and it is
definitely closer to what I need. Although, the result is
actually the opposite of what I want. The result I got
is 'YYYYYYYYSFWE' while I wanted is '********YYYY'. I
want to keep all asterisks and replace those in the string
value that is NOT an asterisk with 'Y'. How would I do
that using the select you sent me?
Thanks,
bpdee

>--Original Message--
>You probably want to do something like this. I've got
the first part of the
>replace working, you can probably figure out the second
part. You might
>want to put this into a function...
>The last column in the example is what you're after; the
others are so you
>can follow what I'm doing.
>declare @.myfield varchar(100)
>set @.myfield = '*****100****'
>select CHARINDEX('*', @.myfield),
> PATINDEX('%[^*]%', @.myfield)-1,
> RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1), --
you might want to
>put 100 Y's into the string at the start - depending on
what you expect to
>have in your field
> STUFF(@.myfield, CHARINDEX('*', @.myfield), PATINDEX('%
[^*]%', @.myfield)-1,
>RIGHT('YYYYYYYYYY', PATINDEX('%[^*]%', @.myfield)-1))
>Andre
>
>"bpdee" <anonymous@.discussions.microsoft.com> wrote in
message
>news:150301c4b61a$0be52cb0$a501280a@.phx.gbl...
>
>.
>|||Hi,
You can probably use ASCII function to determine the ASCII value of *...
CREATE TABLE #temp
(a1 int, a2 char(1), a3 char(1))
DECLARE @.position int, @.string char(12)
SET @.position = 1
SET @.string = '********SFWE'
WHILE @.position <= DATALENGTH(@.string)
BEGIN
INSERT INTO #temp SELECT ASCII(SUBSTRING(@.string, @.position, 1)) a1,
CHAR(ASCII(SUBSTRING(@.string, @.position, 1))) a2, 'Y' a3
SET @.position = @.position + 1
END
SELECT a2,a3 FROM #temp
WHERE a1 NOT IN( 42, 44, 32)
Now I guess you can figure out, how you can replace a2 with a3 and transform
them into columns...
Thanks
GYK
"bpdee" wrote:

> Hi Rick,
> Thank you for your response. Although, I would not know
> other than it is a non-asterisk value that is in the
> string that I need to replace with the value of 'Y'. It
> could be any alphanumeric value and any combination of it
> that is in the string that I need to replace. It could
> also be anywhere in the string.
> Thanks,
> bpdee
>
> message
>|||Here is some code that should work...
You could make this into a sproc or a function...
DECLARE @.OrgCol varchar(100)
@.Count int,
@.NewCol varchar(100)
SELECT @.OrgCol = ColumnName FROM TableName
SET @.Count = 1
SET @.NewCol = ''
WHILE (@.Count < DATALENGTH(@.OrgCol))
BEGIN
IF (SUBSTRING(@.OrgCol, @.Count, 1) <> '*')
SET @.NewCol = @.NewCol + 'Y'
ELSE
SET @.NewCol = @.NewCol + SUBSTRING(@.OrgCol, @.Count, 1)
SET @.Count = @.Count + 1
END
-- Write your update statement here or return @.NewCol if a UDF
Rick Sawtell
MCT, MCSD, MCDBA|||Sorry, I didn't read well enough.
While I still think functions are efficient and might be a good thing to
use, if I can accomplish what I want in a query, I'll generally pick that
route first. That said, give this a whirl. It worked for me regardless of
whether or not there were asterisks at the beginning or end of the string,
and no matter how many there were. The reason I'd opt for a function is
this isn't very intuitive to understand when you look at it, and you can
probably take a more intuitive approach in a function, like the examples
that have been given by others here.
declare @.myfield varchar(100)
set @.myfield = '******SFWE'
select STUFF(@.myfield, PATINDEX('%[^*]%', @.myfield), 4, RIGHT('YYYYYYYYY
Y',
(LEN(@.myfield) - ((PATINDEX('%[^*]%', @.myfield)-1) + (PATINDEX('%[^*
]%',
REVERSE(@.myfield))-1)))))
I hope this helps.
Andre
"bpdee" <anonymous@.discussions.microsoft.com> wrote in message
news:155701c4b627$8638b6a0$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi Andre,
> Thank you for your response. You are correct that the
> second one is the one I need to solve my problem. I ran
> the select statement against my database and it is
> definitely closer to what I need. Although, the result is
> actually the opposite of what I want. The result I got
> is 'YYYYYYYYSFWE' while I wanted is '********YYYY'. I
> want to keep all asterisks and replace those in the string
> value that is NOT an asterisk with 'Y'. How would I do
> that using the select you sent me?
> Thanks,
> bpdee
>
> the first part of the
> part. You might
> others are so you
> you might want to
> what you expect to
> [^*]%', @.myfield)-1,
> message|||Hi Rick,
Thank you so much, Rick! This did the trick.
Thanks again,
Bettina
"Rick Sawtell" wrote:

> Here is some code that should work...
> You could make this into a sproc or a function...
> DECLARE @.OrgCol varchar(100)
> @.Count int,
> @.NewCol varchar(100)
> SELECT @.OrgCol = ColumnName FROM TableName
> SET @.Count = 1
> SET @.NewCol = ''
> WHILE (@.Count < DATALENGTH(@.OrgCol))
> BEGIN
> IF (SUBSTRING(@.OrgCol, @.Count, 1) <> '*')
> SET @.NewCol = @.NewCol + 'Y'
> ELSE
> SET @.NewCol = @.NewCol + SUBSTRING(@.OrgCol, @.Count, 1)
> SET @.Count = @.Count + 1
> END
> -- Write your update statement here or return @.NewCol if a UDF
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

REPLACE SUBSTRING BETWEEN TWO CHARACTERS

Hi.
I have to replace substrings which are between two characters.
For example: I have to replace or remove all the characters that are
between ' \':
So, i have the string abcde\fgh\ikl and i want to get: abcdeikl.
I use the following select statement:
REPLACE(ColumnName, SUBSTRING([ColumnName, CHARINDEX('\', ColumnName),
CHARINDEX('\', ColumnName, CHARINDEX('\', ColumnName) + 1) -
CHARINDEX('\', ColumnName + 1), '')
Is there any more efficient way?
Thanks.Hi
declare @.d varchar(50)
set @.d='abkcde\fgh\ikl'
select STUFF(@.d,start,endpos-start,'')
from
(
select charindex('',@.d,1) as start,
len(@.d)-charindex('',reverse(@.d),1)+2 as endpos
) as der
<stelioshalkiotis@.yahoo.gr> wrote in message
news:1132833870.358155.145790@.g47g2000cwa.googlegroups.com...
> Hi.
> I have to replace substrings which are between two characters.
> For example: I have to replace or remove all the characters that are
> between ' ':
> So, i have the string abcde\fgh\ikl and i want to get: abcdeikl.
> I use the following select statement:
> REPLACE(ColumnName, SUBSTRING([ColumnName, CHARINDEX('', ColumnName),
> CHARINDEX('', ColumnName, CHARINDEX('', ColumnName) + 1) -
> CHARINDEX('', ColumnName + 1), '')
> Is there any more efficient way?
> Thanks.
>|||or
declare @.d varchar(50)
set @.d='abkcde\fgh\ikl'
Select
Substring(@.d,1,charindex('\',@.d)- 1)+reverse(Substring(reverse(@.d),1,chari
nde
x('\',reverse(@.d))-1))
Madhivanan

Replace Multiple Characters

Hi
I need a select statement replace multiple characters from every row
in a column.
I know about replace :
REPLACE ( 'string_expression1' , 'string_expression2' ,
'string_expression3' ) but the question is
how can i do the replace if there are multiple 'string_expression2' ?
For example:
I use replace when i make a select statement in a table like this:
SELECT *, REPLACE(ColumnName, 'XXX', 'TTT'), AS Expr1,
FROM TableName
if i have the string XXXYYYZZZMMM and i want XXX to be replaced with
TTT and ZZZ to be replaced with OOO. how can i modify this select
statement?
Thanks in advance
.Try
SELECT *,
REPLACE(REPLACE(ColumnName, 'XXX', 'TTT'), 'ZZZ','OOO') AS Expr1,
FROM TableName
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
<stelioshalkiotis@.yahoo.gr> wrote in message
news:1131957553.550242.182530@.g14g2000cwa.googlegroups.com...
> Hi
> I need a select statement replace multiple characters from every row
> in a column.
> I know about replace :
> REPLACE ( 'string_expression1' , 'string_expression2' ,
> 'string_expression3' ) but the question is
> how can i do the replace if there are multiple 'string_expression2' ?
> For example:
> I use replace when i make a select statement in a table like this:
> SELECT *, REPLACE(ColumnName, 'XXX', 'TTT'), AS Expr1,
> FROM TableName
> if i have the string XXXYYYZZZMMM and i want XXX to be replaced with
> TTT and ZZZ to be replaced with OOO. how can i modify this select
> statement?
> Thanks in advance
>
> .
>|||If you know the total number of replacements in advance, you can nest the
REPLACE statements. So, you will write something like REPLACE(REPLACE (...),
..., ...)
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<stelioshalkiotis@.yahoo.gr> wrote in message
news:1131957553.550242.182530@.g14g2000cwa.googlegroups.com...
> Hi
> I need a select statement replace multiple characters from every row
> in a column.
> I know about replace :
> REPLACE ( 'string_expression1' , 'string_expression2' ,
> 'string_expression3' ) but the question is
> how can i do the replace if there are multiple 'string_expression2' ?
> For example:
> I use replace when i make a select statement in a table like this:
> SELECT *, REPLACE(ColumnName, 'XXX', 'TTT'), AS Expr1,
> FROM TableName
> if i have the string XXXYYYZZZMMM and i want XXX to be replaced with
> TTT and ZZZ to be replaced with OOO. how can i modify this select
> statement?
> Thanks in advance
>
> .
>|||Thanks!
It works great!sql

REPLACE Function for Bad Characters?

I need to know if I can use the REPLACE function to remove bad characters from a data column. The data type is text and I would like to remove any comma's or quotes from the data. I assume that this would be the best way to remove these characters.

I've not been able to find much information on the REPLACE function so I'm not sure if the data can be replaced in the same column or if the
replacement data has to be placed in another column.

Any insight into this matter would be appreciated.

TechRickupdate yourtable
set textcol = replace(replace(textcol,','),'''')

if there is no replacement character, the search character is removed

i'm pretty sure you can nest oracle functions (i can't test it because i don't gots no oracle database)

to create a string consisting of a single quote, i think you have to code two consecutive ones, hence the four of them in a row like that

rudy
http://rudy.ca/|||Thanks for the reply.

I think there is a difference however in the SQL that I'm using verses yours. I'm running on SQL 2000 and doing my queries through the Query Analyzer.

The format for the function command you suggested doesn't seem to work properly for me.

In my SAM's Learn SQL in 21 Days book I have this format given:

SELECT LASTNAME, REPLACE(LASTNAME, 'ST') REPLACEMENT
FROM CHARACTERS;

OUTPUT:
LASTNAME REPLACEMENT
----- -----
CHRISTINE CHRIINE
ADAMS ADAMS
COSTALES COALES

------------------

The problem I have with this is that it takes the replacement data and puts it into another column. I want to keep the updated data in the same column. I guess I may need to live with a 'replacement' column and disregard the original?

Thanks again for your help.

TechRick|||dude, you posted in the "SQL and PL/SQL" forum, you should've posted in the "Microsoft SQL/Server" forum

i naturally assumed oracle, since REPLACE is an oracle function

in sql.server, the replacement string is not optional, so you have to specify it as a zero-length string

see http://msdn.microsoft.com/library/en-us/tsqlref/ts_ra-rz_76lh.asp

so try

update yourtable
set textcol = replace(replace(textcol,',',''),'''','')

:cool:|||Sorry for the confusion. I didn't realize I was in the wrong place.

I'm a newbie to SQL and have only been using it for less than 2 weeks. I'm not yet familiar with all the differences in SQL formats.

I'll try to find my way to the correct forum in the future.

Thanks again for your help.

TechRick|||hey, no problem, i just thought i'd mention why you got an oracle answer instead of an sql/server answer the first time (i am not the moderator of either of these two forums)

so, did the sql/server version work?|||Rudy,

I just finished testing it and sure enough it did the trick! Thanks much for all the help.

One more question, why did you structure it this way:

replace(replace(textcol,',',''),'''','')

I thought it would need to be replace (texcol, ",", " ")

Why the extra replace and the added ),'''','')?

Just wondering.

Thanks again,
TechRick|||one removes commas, the other removes quotes

nesting them passes the result of one into the other

beats using two update stements, eh

rudy|||Very nice.

I should have guessed that was the case.

Thanks again for all your help.

Best Regards,
TechRick|||I think we can also do this using TRANSLATE Function. I tested this one only in ORACLE and not in SQL-Server. I dont know whether we have one similar funtion in SQL-Server.

SELECT emp_id ,
emp_name,
TRANSLATE( emp_name, '%!@.#$^', ' ' )
FROM employee_table ;

Regards,
Sreekon.

Replace function and regular expressions

Is it possible to use the REPLACE function in SQL Server 2000 so that
it returns a string containing only alpha-numeric characters (much
like using regular expressions)?

Thank you in advance for any suggestion.

Darren.I think you'll have to do it iteratively:

CREATE FUNCTION dbo.CleanChars
(@.str VARCHAR(8000), @.validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^'
+ @.validchars +']%',@.str), 1) ,'')
RETURN @.str
END

GO

SELECT dbo.CleanChars('TESTING1234','0-9')

--
David Portas
SQL Server MVP
--

replace first few characters

how do i replace "Building" with bld ?

i will getting the data like this:

Buildling 100
Buildling 200
Buildling 300
Buildling 400
Buildling 500

i want like this:

Bld 100
Bld 200
Bld 300
Bld 400
Bld 500

in other words, where ever building replace with BLD

i'm using SRS 2000

thanks a lot

Hi,

You can do this either in the query or in the report.

In the query you can use the SQL REPLACE() function http://www.sqlteam.com/item.asp?ItemID=7386 if you are using a SQL data source. Or in the report you can use an expression like =Fields!MyValue.Value.ToString().Replace("Building", "Bld")

Thanks, Jon

|||

here is the error:

c:\Reporting.rdl The value expression for the textbox ‘txtBuilding’ refers to the field ‘bldg’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

|||You either have an invalid reference (i.e. Fields!Bldg does not exist), or you are trying to reference the field outside of a proper scope (i.e. you have added it outside of a table/matrix/list without wrapping it in an aggregate function such as SUM() or FIRST())|||

=First(Fields!BLDG.Value.ToString().Replace("BUILDING", "BLD"), "db")

the above one works but it display all my 20,0000 records with the same building number and in fact i have different building numbers but it shows all building the same number

do you know why is not changing?

|||

myGreenBird wrote:

=First(Fields!BLDG.Value.ToString().Replace("BUILDING", "BLD"), "db")
...
do you know why is not changing?

Yes..

If you use =First(somecolumn,datasource) you will always get the first value that is returned from the Dataset..

What about

=Fields!BLDG.Value.ToString().Replace("BUILDING", "BLD") ?

You have to use this in a table or a list (beside your other columns), you cannot use it in page header or footer.