Replacing Null values in query

Hello,

I'm using the query wizard in VB.net to write a query for SQL CE. I want to replace null values with text. I expected the COALESCE function to do this but I get an error message saying its not a valid function. This is a sample.

Select COALESCE(table.Name,'No Name') as Name from table

Any help appreciated

Thanks

Wouldn't ISNULL() do the trick for you?|||I'm connected to SQL compact. isnull() will only return a logical value. NVL doesn't work in SQL compact and when I use coalesce the editor bangs square brackets around it and returns an error message|||

You can use something like

SELECT CASE WHEN c1 IS NULL THEN 'No name' ELSE c1 END AS EXPR1
FROM t1

COALESCE not being recognized by querydesigner looks like a bug. I'll log it. Thanks for reporting!

|||

Pragya Agarwal [MSFT] wrote:

COALESCE not being recognized by querydesigner looks like a bug. I'll log it. Thanks for reporting!

I checked in the newer Visual Studio 'Orcas' builds and this bug has already been fixed :- ).

|||

Many thanks for your help

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 a single quote

How can use the REPLACE function to replace a single quote in a string in
T-SQL? I have tried using double quotes, but cannot get it to work. I am
trying to replace all the single quotes in at text field with a question
mark. for instance:
REPLACE(myfieldwithquotes,"'",'?')
--
JasonUse two single quotes:
REPLACE(myfieldwithquotes,'''','?')
ML
http://milambda.blogspot.com/|||escape single quote with single quote
replace('''','?')
--
-Omnibuzz
--
Please post ddls and sample data for your queries and close the thread if
you got the answer for your question.
"JasonDWilson" wrote:

> How can use the REPLACE function to replace a single quote in a string in
> T-SQL? I have tried using double quotes, but cannot get it to work. I am
> trying to replace all the single quotes in at text field with a question
> mark. for instance:
> REPLACE(myfieldwithquotes,"'",'?')
> --
> Jason|||That is so you can escape them
REPLACE(myfieldwithquotes, CHAR(39), CHAR(39)+CHAR(39))
or to replace with a blank space
REPLACE(myfieldwithquotes, CHAR(39), '')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?

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

Replacement of functions by procedures for SQL 7.0

Hello to everybody. I have problems with porting my sql function to elder version of SQL server.
7.0 doesnt have functions at all

Here is fragment of function I used before:

SELECT @.MessageCount = COUNT(RecordID)
FROM Msgs
WHERE dbo.GetGroup(Type) = dbo.GetGroup(@.Type)
AND RecordID <> @.RecordID
AND Receiver = @.Receiver
AND StartDate IS not NULL
AND dbo.CheckDateTime(
dbo.ExtractDate(@.StartDate),
dbo.ExtractDate(@.EndDate),
dbo.ExtractTime(@.StartDate),
dbo.ExtractTime(@.EndDate),
dbo.ExtractDate(StartDate),
dbo.ExtractDate(EndDate),
dbo.ExtractTime(StartDate),
dbo.ExtractTime(EndDate),
dbo.UsesTime(@.Type),
dbo.UsesTime([Type])) = 1

RETURN @.MessageCount

I replaced most of functions used in query with procedures. But how to implement query in better way if I cannot use functions?You should use scalar UDFs sparingly especially in SELECT statements. It hurts performance, limits plan choices and complicates performance troubleshooting. And using scalar UDFs for simple expressions is even more problematic. So remove the UDFs and replace them with the expression directly so it will work in any version of SQL Server and you can get the best performance also.

Replacement of functions by procedures for SQL 7.0

Hello to everybody. I have problems with porting my sql function to elder version of SQL server.
7.0 doesnt have functions at all

Here is fragment of function I used before:

SELECT @.MessageCount = COUNT(RecordID)
FROM Msgs
WHERE dbo.GetGroup(Type) = dbo.GetGroup(@.Type)
AND RecordID <> @.RecordID
AND Receiver = @.Receiver
AND StartDate IS not NULL
AND dbo.CheckDateTime(
dbo.ExtractDate(@.StartDate),
dbo.ExtractDate(@.EndDate),
dbo.ExtractTime(@.StartDate),
dbo.ExtractTime(@.EndDate),
dbo.ExtractDate(StartDate),
dbo.ExtractDate(EndDate),
dbo.ExtractTime(StartDate),
dbo.ExtractTime(EndDate),
dbo.UsesTime(@.Type),
dbo.UsesTime([Type])) = 1

RETURN @.MessageCount

I replaced most of functions used in query with procedures. But how to implement query in better way if I cannot use functions?You should use scalar UDFs sparingly especially in SELECT statements. It hurts performance, limits plan choices and complicates performance troubleshooting. And using scalar UDFs for simple expressions is even more problematic. So remove the UDFs and replace them with the expression directly so it will work in any version of SQL Server and you can get the best performance also.sql

replace value of (sq:variable("@variable")) with ""

replace value of(/node/sql:variable("@.varname")/@.Status)[1] with
"pass"'

gives me The XQuery syntax '/function()' is not supported.

Is it not supported?

If it is not supported what is the best way to update a specific node out of many based on certain criteria?

like update Student Grade to A for Student node with name "John" and not any other, without having to use dynamic SQL.

Tim

|||

reproducing answer to this by Pohwan on another discussion alias. This is exactly what I wanted and it works if somebody else is in is same boat

*****************

Workaround,

/node/*[local-name()=sql:variable("@.varname")]/@.Status

instead of

/node/sql:variable("@.varname")/@.Status

--
Pohwan Han. Seoul.

**************

|||

Thanks, that worked wonders for me

replace value of (sq:variable("@variable")) with ""

replace value of(/node/sql:variable("@.varname")/@.Status)[1] with
"pass"'

gives me The XQuery syntax '/function()' is not supported.

Is it not supported?

If it is not supported what is the best way to update a specific node out of many based on certain criteria?

like update Student Grade to A for Student node with name "John" and not any other, without having to use dynamic SQL.

Tim

|||

reproducing answer to this by Pohwan on another discussion alias. This is exactly what I wanted and it works if somebody else is in is same boat

*****************

Workaround,

/node/*[local-name()=sql:variable("@.varname")]/@.Status

instead of

/node/sql:variable("@.varname")/@.Status

--
Pohwan Han. Seoul.

**************

|||

Thanks, that worked wonders for me

Replace temp table with inline table-value function

PREFACE: We are getting rid of the temporary tables so I don't need to be
convinced not to use them.
In our current system we have a pattern where a temporary table is created
in one or more "calling" procedures and populated with selected keys of a
table and in the "called" procedure, those keys (from the temporary table)
are joined to a set of tables to produce a detail result set. Multiple
"calling" procedures exist that populate the temp key table based on various
criteria, but they all call the same "called" procedure which centralizes
the logic for pulling together the details.
This method causes concurrency problems because the "called" procedure is
re-compiled every time because it references a temporary table defined in
another procedure.
The method I have come up with to get rid of the temporary tables but to
still centralize and re-use the detail logic is as follows:
I have created an inline table-value function that replaces the common
"called" procedure in the above scenario. Now in the "calling" procedures,
instead of populating a temp table with keys and calling the "called"
procedure, they simply join the criteria with the user defined function,
selecting the needed fields from the results. Looking at the query plan,
this seems very optimal because it appears that the whole query (the key
criteria and the user-defined function statements) are merged together and
an execution plan is generated for them as a whole (instead of as 2 discrete
statements), giving me the best of both worlds: centralized, re-usable
logic, and a good execution plan.
My question is: Is there anything inherently non-scalable about using SQL
Server 2000's inline table-value function that will burn me under heavy
load?
Thanks,
Mike Jansen
(Abbreviated DDL follows)
OLD WAY
---
CREATE TABLE dbo.Entities
(
entity_pk int IDENTITY(100, 1) NOT NULL CONSTRAINT pk_Entities PRIMARY
KEY,
blah
blah
)
GO
CREATE PROCEDURE dbo.spGetEntityDetails
AS
SELECT
E.entity_pk, E.blah, E.blah, D.blah, D.blah
FROM
#EntityList E
INNER JOIN EntityDetails D ON E.entity_pk = D.entity_pk
GO
CREATE PROCEDURE dbo.spSeeOneGroupOfEntities
AS
CREATE TABLE #EntityList (entity_pk int NOT NULL PRIMARY KEY)
INSERT #EntityList (entity_pk)
SELECT E.entity_pk
FROM Entities E INNER JOIN .....
WHERE E.blah = 'one kind'
EXEC dbo.spGetEntityDetails
DROP TABLE #EntityList
GO
CREATE PROCEDURE dbo.spSeeAnotherGroupOfEntities
AS
CREATE TABLE #EntityList (entity_pk int NOT NULL PRIMARY KEY)
INSERT #EntityList (entity_pk)
SELECT E.entity_pk
FROM Entities E INNER JOIN .....
WHERE E.blah = 'another kind' AND ...
EXEC dbo.spGetEntityDetails
DROP TABLE #EntityList
GO
NEW WAY
---
CREATE FUNCTION dbo.fnGetEntityDetails()
RETURNS TABLE
RETURN
(
SELECT D.entity_pk, D.blah, D.blah, D2.blah, D2.blah
FROM EntityDetails D INNER JOIN EntityDetails2 D2 ON ....
)
GO
CREATE PROCEDURE dbo.spSeeOneGroupOfEntities
AS
SELECT
E.entity_pk, D.blah, D.blah
FROM
Entities E INNER JOIN dbo.fnGetEntityDetails() D ON E.entity_pk =
D.entity_pk
WHERE
E.blah = 'one criteria' AND ...
GO
CREATE PROCEDURE dbo.spSeeAnotherGroupOfEntities
AS
SELECT
E.entity_pk, D.blah, D.blah
FROM
Entities E INNER JOIN dbo.fnGetEntityDetails() D ON E.entity_pk =
D.entity_pk
WHERE
E.blah = 'another criteria' AND ...Noting to self that my descriptions can be a little long (and hence take too
long to read...), here's my question succinctly:
Is there anything inherently non-scalable about using SQL Server 2000's
inline table-value function that will burn me under heavy
load?
Thanks,
Mike|||> Is there anything inherently non-scalable about using SQL Server 2000's inline table-valu
e
> function that will burn me under heavy
> load?
Not that I know of. I haven been told that they are optimized and used in th
e same way as views (and
they were called parametized views during early stages of development of SQL
Server 2000). I can't
offer proof or similar, I'm afraid, but that are my experiences and what I h
ave been told.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:O19%23vR5kFHA.1044@.tk2msftngp13.phx.gbl...
> Noting to self that my descriptions can be a little long (and hence take t
oo long to read...),
> here's my question succinctly:
> Is there anything inherently non-scalable about using SQL Server 2000's in
line table-value
> function that will burn me under heavy
> load?
> Thanks,
> Mike
>|||Mike Jansen wrote:
> Noting to self that my descriptions can be a little long (and hence take t
oo
> long to read...), here's my question succinctly:
> Is there anything inherently non-scalable about using SQL Server 2000's
> inline table-value function that will burn me under heavy
> load?
I agree with Tibor.
A couple of years ago, I did a good bit of tuning work on a system that
made heavy use of udf's. My experience was positive with inline
table-valued functions. The plans produced looked to me like the
optimizer treats them as it would a view or a derived table. It can
"see inside" them and optimize to the base table level. I think I saw
this behavior even when nesting functions.
Multistatement table-valued functions, on the other hand, seemed to be
a black box to the optimizer. That makes sense. How could he (the
optimizer) evaluate the logic that could be inside a multi-statement
function. Instead, it uses a table scan of whatever table variable is
returned.
Best of luck
Payson

> Thanks,
> Mike

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 Oracle function in SQLServer 2000

Hi everyone. I'm currently migrating our system from Oracle to SQLServer. How can I switch the REPLACE Oracle function? Is there any function in SQLServer that do the same thing, or any function that has something near the REPLACE? I was trying the STUFF function, but it uses different parameters, and the migration will be stuff(something like 18k of SQL lines :( ).
Thanks in advance and Best Regards
Rafael Mauricio Nami
Have you had a look at replace?|||Thanks, I've got the wrong SQLServer book :)

Replace negative values

hi,
In the result of a function in my query, there are negative numbers.
How do I replace them with a 0 or is there a function like ISNULL that replaces the values that are negative?
thanks,
maartenYou can modify select statement to something like this-

select item, qty, value=
case
when value < 0 then '0' else value
end
from test

Roshmi Choudhurysql

Replace List

Hi,
I was wondering if SQL Server had a function similar to the ReplaceList()
function in ColdFusion. If you don't know anything about ColdFusion, I'll
explain what I need.
I have a field that I need to verify for "bad" characters from a list and
replace them with the "good" versions.
For example:
ArrayBad('badchar_1', 'badchar_2', 'badchar_3', ...);
ArrayGood('goodchar_1', 'goodchar_2', 'goodchar_3', ...);
Replace all the listed bad characters with their equal good version
repectively.
Anyone now how I can do this in a UDF format?
TIA,
EricT-SQL doesn't have any array or list datatypes. The most direct approach is
that you'll have to nest them. E.g.
CREATE FUNCTION dbo.CleanWord
(
@.dirtyWord VARCHAR(64)
)
RETURNS VARCHAR(64)
AS
BEGIN
DECLARE @.cleanWord VARCHAR(32);
SET @.cleanWord =
REPLACE(
REPLACE(
@.dirtyWord,
'bleep','@.#%$'),
'beep','#^&@.');
RETURN @.cleanWord;
END
GO
SELECT dbo.CleanWord('who the bleep did this beep?');
GO
DROP FUNCTION dbo.CleanWord;
GO
"Eric D." <EricD@.discussions.microsoft.com> wrote in message
news:0BD58B41-D2D8-45F6-ADDA-5707717C7C65@.microsoft.com...
> Hi,
> I was wondering if SQL Server had a function similar to the ReplaceList()
> function in ColdFusion. If you don't know anything about ColdFusion, I'll
> explain what I need.
> I have a field that I need to verify for "bad" characters from a list and
> replace them with the "good" versions.
> For example:
> ArrayBad('badchar_1', 'badchar_2', 'badchar_3', ...);
> ArrayGood('goodchar_1', 'goodchar_2', 'goodchar_3', ...);
> Replace all the listed bad characters with their equal good version
> repectively.
> Anyone now how I can do this in a UDF format?
> TIA,
> Eric
>

replace funtion

how do i write a replace function that will replace a certain character with a return key (ie what happens when we do Ctrl+return key in SQL Enterprise table... so that the rest of the cell data in the column is on the next line?!

SELECT REPLACE(tasks, '/', '??') AS EXPR1
FROM log_descriptions

what should ?? be?I think you want the char function with the ascii code for line break.|||I alway forget the ANSII code for a line break so I use this instead:
SELECT REPLACE(tasks, '/', '
') AS EXPR1
FROM log_descriptions

Works like a charm :)|||or this, maybe easier to read in a big script :)

declare @.cr char(1)
set @.cr = '
'
select 'a' + @.cr + 'b'|||Char(13)+char(10)|||13 & 10... I'll try to rember that :D

So summing this up we got:
DECLARE @.cr CHAR(1)
SET @.cr = CHAR(13) + CHAR(10)
SELECT 'a' + @.cr + 'b'

Replace Function? SQL2K/EM

Hi,
I need to change some table and field names - is there a way to update
all the occurances in Views and Stored procedures?Update the source code for the views and stored procedures and then redeploy
them. You can use the rows from sysdepends to determine which views and
stored procs are affected by name change of a table.
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1150108178.447216.308320@.f14g2000cwb.googlegroups.com...
> Hi,
> I need to change some table and field names - is there a way to update
> all the occurances in Views and Stored procedures?
>|||Do they have to be updated manually?
Visual Studio has tools to do this automatically, does EM have nothing
similar for its source code ?
Tim Dot NoSpam wrote:
> Update the source code for the views and stored procedures and then redepl
oy
> them. You can use the rows from sysdepends to determine which views and
> stored procs are affected by name change of a table.
> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> news:1150108178.447216.308320@.f14g2000cwb.googlegroups.com...|||> Visual Studio has tools to do this automatically, does EM have nothing
> similar for its source code ?
EM/SSMS will not automatically rename objects because there is nothing on
the server side that will track dependencies. However, this one of the many
new features included in the upcoming Visual Studio 2005 Team Edition for
Database Professionals.
See http://msdn.microsoft.com/vstudio/t...ro/default.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1150110493.525079.29150@.i40g2000cwc.googlegroups.com...
> Do they have to be updated manually?
> Visual Studio has tools to do this automatically, does EM have nothing
> similar for its source code ?
> Tim Dot NoSpam wrote:
>

Replace function: replacing an apostrophe with whatever

hey

i'm having a problem with a stored procedure, to cut a long story short i need to replace apostrophe's in my text, like so

SET @.prOtherValue = REPLACE(@.prOtherValue,''', '''')

this doesn't work tho

whats the work around

cheers!!!!

SET @.prOtherValue = REPLACE(@.prOtherValue,''', '')

|||

nar.. no good

this is the context i have it in

SET @.prOtherValue = REPLACE(@.prOtherValue,''','')

EXEC( 'UPDATE #_AllParticipantResponses SET '+@.qcTitle+' = '''+@.prOtherValue+''' WHERE participantId='+@.globalparticipantId+'')

the problem is in @.prOtherValue, sometimes i'll have the word isn't or whatever word, which contains an apostrophe

if you copy and paste the above into query analyzer, watch what hapens to the EXEC statement if you have an apostrophe where i have bolded

|||

SET @.prOtherValue=REPLACE(@.prOtherValue,'''','')

EXEC('UPDATE #_AllParticipantResponses SET '+ @.qcTitle+' = '''+ @.prOtherValue+''' WHERE participantId= '+convert(Varchar,@.globalparticipantId))

|||

hehe...

nar, you have 2 appostrophes for the second argument

in my database right, i have a column called OtherValue. in this column, i have words like isn't can't it's ( focus on the single appostrophe in each word)

when i try and pass words like isn't to my @.prOtherValue, the update statement fails because the @.prOtherValue value contains an appostrophe ( ' )

apostrophe's on t-sql have some sort of formatting value

when i try to execute the update statement, if i have an appostrophe in a word, it terminates

what i need to do isREPLACE(@.prOtherValue,''','whatever') but you can't have'''

cheers!!!

|||

Replace(prOtherValue,char(39),'|')

|||

Finally got a chance to try it out

Thanks for that

Replace function not working

Hi,
I posted a request here and am still working on it when I landed on this bug.

select top 10 replace(comma_separated_string,',','giveaverylongp assagehere') from table

The function works fine if the comma separated string is small or if the passage is small. It fails for long passages..

Is this a mssql bug?What's long? Replace works fine for me on 8000. Does it break off at 1024 in the Analyzer, while it's len(..) says otherwise?sql