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
>
>
No comments:
Post a Comment