Replacing a character

Hi

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

Thanks!

Santhosh

Santhosh,

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

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

rgds,

v r kumar

Replace, the character, and yea...

I have the following:

------

WHILE PATINDEX('%,%',@.Columns)<> 0BEGIN
SELECT @.Separator_position = PATINDEX('%,%',@.Columns)
SELECT @.array_Value =LEFT(@.Columns, @.separator_position - 1)
SET @.FieldTypeID = (SELECT FieldTypeIDFROM [Form].[Fields]WHERE FieldID = (CAST(@.array_ValueAS INT)))
SET @.FieldName = (SELECT [Name]FROM [Form].[Fields]WHERE FieldID = @.array_Value)
print'arry value' +CONVERT(VarChar(500), @.array_value)
print'FieldTypeID: ' +CONVERT(VARCHAR(500), @.FieldTypeID)
PRINT'FieldName: ' + @.FieldName
BEGIN
IF @.FieldTypeID = 1OR @.FieldTypeID = 2OR @.FieldTypeID = 3OR @.FieldTypeID = 9OR @.FieldTypeID = 10OR @.FieldTypeID = 7
BEGIN
SET @.InnerItemSelect =' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[ItemDetailFieldRecords] IDFR WHERE IDFR.ItemDetailID = ID.ItemDetailID AND IDFR.FieldID = ' + @.array_Value +') AS ''' + @.FieldName +''' '
SET @.InnerTaskSelect =' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[TaskFieldRecords] TFR WHERE TFR.TaskID = T.TaskID AND TFR.FieldID = ' + @.array_Value +') AS ''' + @.FieldName +''' '
END
ELSE IF @.FieldTypeID = 4OR @.FieldTypeID = 8--DropDownList/RadioButtonlist
BEGIN
SET @.InnerItemSelect =' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[ItemDetailFieldListRecords] IDFLR ON FFLV.FieldListValueID = IDFLR.FieldListValueID WHERE IDFLR.ItemDetailID = ID.ItemDetailID AND FFLV.FIeldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
SET @.InnerTaskSelect =' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[TaskFieldListRecords] TFLR ON FFLV.FieldListValueID = TFLR.FieldListValueID WHERE TFLR.TaskID = T.TaskID AND FFLV.FIeldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
END
ELSE IF @.FieldTypeiD = 5--Cascading
BEGIN
SET @.InnerItemSelect =' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[ItemDetailFieldCascadingRecords] IDFCR ON IDFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE IDFCR.ItemDetailID = ID.ItemDetailID AND LT.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
SET @.InnerTaskSelect =' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[TaskFieldCascadingRecords] TFCR ON TFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE TFCR.TaskID = T.TaskID AND LT.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
END
ELSE IF @.FieldTypeiD = 6--ListBox
BEGIN
SET @.InnerItemSelect =' (SELECT i.[CSV] FROM @.ItemDetailLV i WHERE i.ID = ID.ItemDetailID AND i.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
SET @.InnerTaskSelect =' (SELECT it.[CSV] FROM @.TaskLV it WHERE it.ID = T.TaskID AND it.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
END
ELSE IF @.FieldTypeID = 11--Users
BEGIN
SET @.InnerItemSelect =' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[ItemDetailUserRecords] IDUR ON SU.UserID = IDUR.UserID WHERE IDUR.ItemDetailID = ID.ItemDetailID AND IDUR.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
SET @.InnerTaskSelect =' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[TaskUserRecords] TUR ON SU.UserID = TUR.UserID WHERE TUR.TaskID = T.TaskID AND TUR.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
END
ELSE IF @.FIelDTypeID = 12--Group
BEGIN
SET @.InnerItemSelect =' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[ItemDetailGroupRecords] IDGR ON SG.GroupID = IDGR.GroupID WHERE IDGR.ItemDetailID = ID.ItemDetailID AND IDGR.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
SET @.InnerTaskSelect =' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[TaskGroupRecords] TGR ON SG.GroupID = TGR.GroupID WHERE TGR.TaskID = T.TaskID AND TGR.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
END
END
PRINT'Inner Item Select:' + @.InnerItemSelect
PRINT'Inner Task Select:' + @.InnerTaskSelect
SET @.IDSelect = @.IDSelect + @.InnerItemSelect +', '
SET @.TSelect = @.TSelect + @.InnerTaskSelect +', '
SELECT @.Columns = STUFF(@.Columns, 1, @.separator_position,'')

END 

-----

That is only part of a large query that writs a SQL Query to a column in a Database. That Query (in the column) is just ran normally so I don't need to compile it each time I want to run it.

THe problem I have is @.FieldName might be: ryan's field.

That apostrophe is killing me because the SQL keeps it asryan's field, notryan''s field(note the 2 apostrophes). I cannot do: REPLACE(@.FieldName, ''', '''') because it's not closing the apostrophes. Is there an escape character that I can use to say only one: ' ?

Would the only solution be to put: ryan''s field into the Database, and just format it properly on the output?

Thanks.

What I normally do is replace it with an obsure character like a ^ or | and then properly format it on the output.

|||

Thought about that, but I'm not a fan of putting obscure characters in the system. It makes it a pain later on... and seeing as we have more than 300 tables and 900 Stored procedures i'm not really looking forward to working with it that way.

|||

You best bet would be to replace the single quotes with doubles before you concatenate.

|||

I figured it out:

PRINT REPLACE(@.FieldName, '''', '''''')

I didn't think this would work as I assumed it woudl look for the double apostrophes, but it escapes it to the single, and then replaces with the 2. Worked like a charm :P

Replace the First Character

Hi All,
My query is
Select ID, ProdName from Product
I would like to replace the first character from ProdName (in every row)
with *.
Please let me know how to do it.
Thanks.SELECT ID, '*'+SUBSTRING(ProdName,2,8000) as ProdName FROM Product
Razvan|||On Sun, 18 Sep 2005 02:22:26 -0700, msnews wrote:

>Hi All,
>My query is
>Select ID, ProdName from Product
>I would like to replace the first character from ProdName (in every row)
>with *.
>Please let me know how to do it.
>Thanks.
>
Hi msnews,
Here's another way:
SELECT ID, STUFF(ProdName, 1, 1, '*') FROM Product
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

replace special character

I'm trying to remove the special character from a varchar
select replace(my_col,'',' ')
this works, but the problem is that it also seems to replace the 'normal' y
(Database is case sensitive)
Why is that?I'm guessing a bit here, since I don't have that much experience outside of the default collation for SQL. I think your collation is probably Case Sensitive, Accent Insensitive (CS_AI) (see SQL Collation Name in BOL). I think what you will have to do is perform a binary comparison of your search string to ensure that you are only replacing the desired character. You will probably have to perform the comparison in the WHERE clause, since I don't see a binary option for the REPLACE function.

Sorry, that's not much to go on; I hope it might lead you in the right direction.

Regards,

hmscott|||Try this:

select replace(my_col,char(255),'')|||the char(255) has the same effect,
but changing the collation to AS does the trick

Thanks for the replies

Replace null value

I have NULL value of data field, and I want to replace with '-' character in the crystal report. I try to write this formula "IIF(IsNull(table.field),"-",(table.field))", but the result still Null(blank). Can any body help me?
Thank'sOne option is to create a formula, and then place the formula in the report where the current field is located.

The formula would be:

if isnull(table.field) then "-"
else table.field|||Hi abstract, thanks for ur reply. Now i can fix the problem|||I don't think you understood what I was trying to explain. You need to create a Formula Field; NOT create a formula in the suppress box.

I have used this before, so I know it works.

abstract|||Never mind my last post, I misread something.

abstract|||I think this will work if it is in formula

IIF(IsNull(table.field),"-",(table.field))

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 character with new line

I'm relatively new to scripting in Crystal Reports, and was hoping someone might be able to offer some advice with a problem I'm having.

I have a database field that contains a large text string of comments. In this string are tilde characters, ~, and those are supposed to represent when to insert a new line. I'm trying to take that field and format it so that any tildes are replaced with a new line break. Here is what I have currently:

stringVar ItemList := "";
stringVar CommaChar := "";
numberVar i := 0;

For i:= 1 to count({AR_DIM.ORDER_HEADER_NOTES})

Do
(
If {AR_DIM.ORDER_HEADER_NOTES}[i] = "~" then
ItemList := ({AR_DIM.ORDER_HEADER_NOTES}[i]) + "\r\n"
Else
ItemList := ({AR_DIM.ORDER_HEADER_NOTES}[i]);
i = i+1;
) ;

ItemList

The result I get is only a single character, the first character in the original string.

I'm sure it's a rookie mistake, but anyone have any thoughts on what I'm doing wrong?stringVar ItemList := "";
stringVar CommaChar := "";
numberVar i := 0;
numbervar strLen := Length({AR_DIM.ORDER_HEADER_NOTES});

For i:= 1 To strLen

Do
(
If {AR_DIM.ORDER_HEADER_NOTES}[i] = "~" then
ItemList := ItemList + ({AR_DIM.ORDER_HEADER_NOTES}[i]) + "\r\n"
Else
ItemList := ItemList + ({AR_DIM.ORDER_HEADER_NOTES}[i]);
) ;

ItemList

Replace character from string

Hi,

How to replace specific character from a string.
I have '100001'
i want to replace 2nd chracter '0' with '1' .i.e.
Result output would be

'110001'

Can we do it by using oracle function i.e Replace and transalate

Thanks
PaginitYou could change this specific string using REPLACE:

REPLACE('100001','10','11')

But in general, that would not work - e.g. it would not work for the string '100010', because it would change the 2nd and 6th characters both to 1.

To specificy a particular character position for change you must use SUBSTR:

SUBSTR(string,1,1) || '1' || SUBSTR(string, 3)

This takes the first character, appends a '1', and then appends the rest of the string from position 3 (omitting position 2).sql

Rendering PDF format in Chinese character.

Hi,
I have used render method to render the data from data source to PDF format
in chinese character by using MS Gothic font family, but few character in
chinese words are printing as @. symbol.(ie. that character which has single
cote embeded)
Help is requried to print chinese character correctly in PDF format.
Did i need to set any other font family to print chinese character correctly
in PDF? Which font family help in printing chinese character correctly in PDF
file?.
Thanks in advance for the helpers.
Regards,
AnithaHi,
I got the solution, i had set MingLiU font family instead of MS Gothic, now
its working fine.
"Anitha Naidu" wrote:
> Hi,
> I have used render method to render the data from data source to PDF format
> in chinese character by using MS Gothic font family, but few character in
> chinese words are printing as @. symbol.(ie. that character which has single
> cote embeded)
> Help is requried to print chinese character correctly in PDF format.
> Did i need to set any other font family to print chinese character correctly
> in PDF? Which font family help in printing chinese character correctly in PDF
> file?.
> Thanks in advance for the helpers.
> Regards,
> Anitha
>