Special Character Replacement

Mar 3, 2006

shailendra writes "Hi,everbody
lets come to my problem.I have one variable of type "ntext" which contain character "". i want to remove it or replace it with a blank character.

very urgent.....
waiting for the reply
shail"

View 1 Replies


ADVERTISEMENT

Special Character

Dec 8, 2005

Hi,

i have the following problem:

I like to extract all special characters in one table with many fields.
How can I handle this without using 'replace' for each field and many characters ?

Thx. for ur suggestions.

dajm

View 2 Replies View Related

Special Character

Jul 20, 2005

How can I insert by asp a string containing 'That's the stringAsp code:SQL = "insert into tablename (columnA) values ('" & variable & "')"The problem is when variable contains a ' (single quote), it stops thestring definition and get an error.Can I do something?Thanks--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

View 2 Replies View Related

Special Character;strange

Oct 6, 2004

I had to update pricelist at local-db on the basis of prices in another SQL by matching the description. When i JOIN the tables, no record gets displayed, but the descrptions at both tables were exactly the same i believed. Took a lot of time in identifying the discrepancy; the data was like this:

CREATE TABLE #ABC (price money, DESCRIPTION VARCHAR(200))
GO
INSERT #ABC SELECT 19999,'SQL2000
'
GO
SELECT * FROM #ABC WHERE DESCRIPTION ='SQL2000'
--(0 row(s) affected)
SELECT * FROM #ABC WHERE LTRIM(RTRIM(DESCRIPTION))='SQL2000'
--(0 row(s) affected)
select * from #ABC WHERE REPLACE(DESCRIPTION,'
','')='SQL2000'
--(1 row(s) affected)
GO
DROP TABLE #ABC
:rolleyes: I believe, ENTER has some value within a field.

View 14 Replies View Related

Replace Special Character

Feb 3, 2006

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?

View 3 Replies View Related

Special Character '[' In MSSQL 2005

Nov 7, 2007

Hi,

I am using MSSQL 2005 with the compatability mode set to 2000.Now, one of my String columns in a table has data in the following format:

ABC[123][XYZ]

Now issuing the following SELECT query in the table does not work :

Select * from TAB1 where col1 like 'ABC[%'

ie, no row gets selected.However, the following query works:

Select * from TAB1 where col1 like 'ABC%'

Looks like '[' is a special character :( Now, how do i work around this so that i can select strings starting with "ABC[".Please help.

Thanks.

View 5 Replies View Related

To Find The Special Character Inside A Name

Mar 21, 2006

CHALASANI writes "I have a name in my table which is "ra_ja" I want to find the name with the help of the'_' character.

Can you please help with the querry

Sorry with my english
Thank you"

View 4 Replies View Related

Would Special Character Cause Insertion Problem?

Oct 12, 2007

Hi,SQL env: sql server 2000Target column of insertion: varchar(15)Case, a var is made up of a character of the following characters(random selection):A,B,C,D,E,$,!,%,^,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U, V,W,X,Y,Z,&,*,(,)and a few numbers (random selection), and thenvar = ran1&ran2I'm experiencing intermit sql errorIs it because I did not include the string with quotes likevar = "ran1 & ran2"or the first random seletion may include special character and thatmay cause insertion error randomly?Probably the former is more likely. And I've added quotes for thevar (programming language shouldn't really matter, int type usuallywithout quotes while strings need quotes).What's your thought?Thanks.

View 7 Replies View Related

Special Character In Dynamic TSQL

Oct 8, 2007

Hello all



I am trying to update a colunm with the value (Dynamically).



'UPDATE ' + @TABLE + ' SET '+ @FIELD + ' = '''+ @VALUE +'''

well It works fine until @Value contains quotation for instance @value = O'hare Airport
results in termination of the statement because of single quote after O in O'Hare. Is there any way I can see it works


Also suppose if its updating a field which can be say 10 charcters long and when @value has say 15 characters, it terminates. Is there anyway i can avoid this.




View 4 Replies View Related

Transact SQL :: How To Get All Characters Before Special Character

Jul 21, 2015

I would like to know how can i only get the characters before the special character?

For example if the mail id is The.Champ123@gmail.com i need to extract The.Champ123 and if the mail id is TheChamp@gmail.com I need to get TheChamp.So basically i would like to get the characters from the string before '@' character.

View 14 Replies View Related

Patindex To Find Special Character And Next 6 Digits

Dec 6, 2007

I am creating a view to pull data for a UPS integration I am doing. I currently have this view where I pull my data from. All is well accept for my PATINDEX,

Currently I have this PATINDEX So when a user puts this into delivery instructions "#999999" UPS_FINAL returns 999999 which is good for me to use. But The PATINDEX will also grab all kinds of neat stuff out of the delivery instructions field when I really only want whatever is after the # sign in the field.

I am new to sql and dont quite understand how this search is working but I think I am in need of a better way to search the field



Code:


WHEN SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) IS NULL

THEN dbo.Address_Table.ups_code

ELSE SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) END AS UPS_FINAL


-------------------------------------------------------------------------------------
What I am looking to accomplish is a SUBSTRING that will search delivery_instructions



Code:


SELECT

TOP (100) PERCENT P21PLAY.dbo.p21_view_oe_pick_ticket.pick_ticket_no,
P21PLAY.dbo.p21_view_oe_hdr.order_no, P21PLAY.dbo.p21_view_oe_hdr.customer_id,
P21PLAY.dbo.p21_view_oe_hdr.ship2_name, P21PLAY.dbo.p21_view_oe_hdr.ship2_add1,
P21PLAY.dbo.p21_view_oe_hdr.ship2_add2,
P21PLAY.dbo.p21_view_oe_hdr.ship2_city,
P21PLAY.dbo.p21_view_oe_hdr.ship2_state,
P21PLAY.dbo.p21_view_oe_hdr.ship2_zip,
P21PLAY.dbo.p21_view_oe_hdr.po_no,
P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id AS Carrier,
P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id AS Supplier,

P21PLAY.dbo.p21_view_oe_hdr.cod_flag,
P21PLAY.dbo.p21_view_oe_hdr.terms,
P21PLAY.dbo.p21_view_oe_hdr.ship2_country,
P21PLAY.dbo.p21_view_oe_hdr.ship_to_phone,
P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
dbo.Address_Table.ups_code,

-----------Looks for special character and returns next 6 spaces as UPS_Shipper----------

SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%',

P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6)
AS UPS_Shipper,

------------------Checks view for email address or assigns alternate------------------
(CASE WHEN charindex('@', p21_view_contacts.email_address) > 0 THEN p21_view_contacts.email_address ELSE

'email@domain.com' END) AS alternate_address,

'Y' AS QVN, 'email@domain.com' AS failureaddress,

P21PLAY.dbo.p21_view_contacts.email_address,

------------When carrier_id is not one of these # then Null; else ------------------------------

CASE WHEN P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id NOT IN (105188, 105191, 105194, 105197, 105200,

105203, 105206, 105209, 105212) THEN NULL

----------------- Looks for special Character in delivery_instructions; if NULL then ups_code;
ELSE return value from delivery_instructions as UPS_Final--------------------

WHEN SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) IS NULL
THEN dbo.Address_Table.ups_code
ELSE SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) END AS UPS_FINAL


FROM dbo.Address_Table INNER JOIN
P21PLAY.dbo.p21_view_oe_pick_ticket INNER JOIN
P21PLAY.dbo.p21_view_oe_hdr ON P21PLAY.dbo.p21_view_oe_pick_ticket.order_no =

P21PLAY.dbo.p21_view_oe_hdr.order_no ON
dbo.Address_Table.id = P21PLAY.dbo.p21_view_oe_hdr.customer_id LEFT OUTER JOIN
P21PLAY.dbo.p21_view_contacts ON P21PLAY.dbo.p21_view_oe_hdr.contact_id = P21PLAY.dbo.p21_view_contacts.id

WHERE
(P21PLAY.dbo.p21_view_oe_hdr.completed <> 'Y')
AND (P21PLAY.dbo.p21_view_oe_hdr.delete_flag <> 'Y')
AND (P21PLAY.dbo.p21_view_oe_hdr.will_call <> 'Y')
ORDER BY P21PLAY.dbo.p21_view_oe_pick_ticket.pick_ticket_no



Hope this makes since

View 4 Replies View Related

T-SQL (SS2K8) :: Insert Value Generate Special Character

Mar 12, 2014

I have a web page where the user can select the language (FR, EN, BG, ...) in a drop down list.

Next to the drop down list there is a text box where user can type the some text (translation).

User can add several description

On my web page, i have a button this button collect all information create an xml file and save all in database (sql server 2008)

that's work fine for some language => FR, EN and so on

But for bulgarian (bulgare) and greece there are some problem...

Some characters when i display it in sql look like => ???s??. ? d??ta?? a?t?

The value encoded by the user is => Ένωσης. Η διάταξη αυτή

but the result after t sql xpath is => ???s??. ? d??ta?? a?t?

Here is it my sql code where you can find my temporary table and my xml file and my xpath query

declare @tblTranslation table (idDocID int, languageID varchar(10), value varchar(500))
declare @Translations XML

set @Translations = '<?xml version="1.0" ?><Items><Item><eleKey>EN</eleKey><eleValue>This is a test</eleValue></Item><Item><eleKey>FR</eleKey><eleValue>test</eleValue></Item><Item><eleKey>BG</eleKey><eleValue>Ένωσης. Η διάταξη αυτή</eleValue></Item><Item><eleKey>HR</eleKey><eleValue></eleValue></Item><Item><eleKey>RO</eleKey><eleValue></eleValue></Item></Items>'

-- 2) fill the temporary table with information from the xml file

INSERT INTO @tblTranslation(idDocID, languageID, value)
SELECT
1
, Convert(nvarchar(max), i.query('eleKey/text()')) as colKey
, Convert(nvarchar(max), i.query('eleValue/text()')) as colValue
--
FROM @translations.nodes('/Items/Item') as x(i)
SELECT * FROM @tblTranslation

View 3 Replies View Related

T-SQL (SS2K8) :: Replace A Special Character In Results?

Jun 11, 2014

I have an database that is housing a path used to locate an external file. This application was written many years ago and I am now trying to bring the files into the database as a VARBINARY.

The table is holding the path like this "/folder/folder/file"

I am trying to convert that path to "folderfolderfile"

In my Select statement I have

SELECT ProdID, REPLACE (PATH, /, ) FROM dbo.blahblah

The problem is that I can't figure out to make SQL understand that "/" is the character I want to replace.

View 2 Replies View Related

Transact SQL :: Extracting Certain Text After Special Character

May 18, 2015

I would like to write a SQL to extract a text after certain _ special character.Example : Relationship_Person_Type_Aunt

View 9 Replies View Related

Data Access :: Special Character In Password

Jul 9, 2015

There is a ">" character (right-angle bracket) inside my SQL Server password. When I supplied this password to the bcp utility, the ">" character was treated as an output redirection symbol. So the password was truncated at ">" and the bcp output got sent to a file with a name consisting of the rest of the password after ">". I'm using SQL Server 2012. I cannot use Windows authentication due to company policy. Is there a way to resolve this without changing the password?

View 7 Replies View Related

Saving Text With Special Character Into SQL Express Table

Jun 22, 2007

Hi, fellows!



When I need to save a text into a SQL Express table I use the character ' to encote the text. (ex: 'myText' )



How to procedure when the text has already the character ' ? (ex: how to enconte the text Color's car ) ?



Many thanks to any kind of help!

View 3 Replies View Related

NTEXT Special Character SQUARE Appearing Where A Return Should Be. How Do I Fix This?

Sep 17, 2006

I had to import some FileMaker Data into SQL Server 2005 and in the resume field a "SQUARE" special character is appearing everyplace that a return should be.

It has really messed up the formatting. I know how to use the updatetext in a cursor to replace the special character but I don't know what to replace the "SQUARE" special character with so the text is displaying a new line rather then this "SQUARE" character.



Any help would be appreicated.

View 6 Replies View Related

Problems Character Sets / Special Characters Dot.Net &<-&> Mssql-server

Feb 11, 2008

Hi,I come from the "dark side" php/mysql and there often problems withcharacter sets (utf-8, latin...) and storing data in datebase.Exists in the world of dot.net and ms-sql-server similiar problems?To precise: I have to store xml-data in database. Maybe its better toencode (like base64) the strings?Perhaps there are some links to read?Thanks.klaus.

View 3 Replies View Related

How To Specify A Special Character As A Row Delimiter In The Flat File Connection Manager?

Jan 16, 2007

I have a gazillion text files, each with dozens of records. The fields are pipe-delimited, the end of each record is marked by ASCII char code 28.

example:

05|11900307|1|CO|Gervais|Neil|NG8880|F|540|0|0|0|T|0|||F|||F 05|11900307|2|AO|MARSHALL|BRAD|BM7843|F|510|60|0|0|T|0|||F|||F

The " " is the Chr(28). The line above should parse into 2 rows...

**** time passes ****

An interesting discovery - if I paste that " " directly into the row delimiter box, without quotes or brackets, I get two records!

So I found at least one answer.

Is there another?

TIA

View 1 Replies View Related

How To Import In Special Character Delimited Text File By Using SSIS ?

Sep 26, 2007

Hi,

I would like to know how to import in the custom delimited text file by using SSIS.
For example, instead by using tab or comma delimited, I use this character : '¶'
The reason is the delimited format that SSIS provided is too common such as colon, semi colon, tab, comma and pipeline.
I have the data that the user also key in the pipeline there. So I am thinking to separate the field by using this special character, but cannot see if there is anyway to import in by using SSIS.

Please help to share the solution on this :

A¶B¶C
1¶2¶3

thanks
best regards,
Tanipar

View 8 Replies View Related

SQL Server 2012 :: Split String Into Columns Based On Special Character

Dec 4, 2013

How to get the required result in SQL 2012

Create table DBInfo (Path varchar (500))
Insert into DBInfo values('/Data Sources')
Insert into DBInfo values('/Data Sources/SALES')
Insert into DBInfo values('/PRODUCTION')
Insert into DBInfo values('/PRODUCTION/SERVICE')
Insert into DBInfo values('/PRODUCTION/SERVICE/MAINTENANCE')
Insert into DBInfo values('/PRODUCTION/SERVICE/LOGISTICS')

My Expected Output

Column1,Column2,Column3
Data SourcesNullNull
Data SourcesSalesNull
PRODUCTIONNullNull
PRODUCTIONSERVICENull
PRODUCTIONSERVICEMAINTENANCE
PRODUCTIONSERVICELOGISTICS

View 1 Replies View Related

Multiple Rows Combined Into Onerow And Onecolumn Separated By A Special Character

Oct 24, 2007

Hi All :CREATE TABLE TABLEA(Person Varchar(20), Country Varchar(20), SubjectVarchar(20), Type Char(1))INSERT INTO TABLEA VALUES ('Einstein', 'Germany', 'Physics', 'P')INSERT INTO TABLEA VALUES ('Kant', 'Germany', 'Philosophy', 'Q')INSERT INTO TABLEA VALUES ('Kafka', 'Germany', 'Writer' , 'W')INSERT INTO TABLEA VALUES ('Aristotle', 'Greece', 'Philosophy', 'Q')INSERT INTO TABLEA VALUES ('Archimedes', 'Greece', 'Physics', 'P')INSERT INTO TABLEA VALUES ('Homer', 'Greece', 'Writer' , 'W')SELECT * FROM TABLEAI am on SQL 2000.I need an output where i have to have a resultset grouped on Type, butthe results in one row.In the resultset I needTypeP PersonType P Country, Type Q Person, Type Q Country, TypeW Person Type W Country---------------------------------------------------------------------------------------------------------------------Einstein:ArchimedesGermany:GreeceKant:Aristotle Germany:GreeceKafka:HomerGermany:Greece************************************************** *************I have written a puesdo-cursor code to do the same, but if there is away to do as a set operation, that would be greatPlease select as a whole and past in query analyser as the resultsetis all overlaid when i paste in this box.Thank youRS

View 2 Replies View Related

Special Character Problems With SSIS Data Reader Using ODBC Driver For DB2

Sep 5, 2007



Hi,
in SSIS I read data from a DB2 database on AS400 using the Client Access ODBC Driver for DB2 from IBM and write it to a SQL Server database. Since it does not work using the odbc driver as data source directly, I use a data reader component with .net providersodbc.
Some special characters were not translated correctly when read from DB2. They show up as ? in the SQL Server target table.


I tried to change the client locale in the ODBC connection properties but it did not help me. I tried changing all other settings in odbc but it still does not work.


In dts I could source all the data without this problems and good speed using the same nodbc driver.


The OLEDB Providers delivered with SSIS do not work in SSIS or I am too stupid to configure them correctly. They are even too slow as I explained above.


I cannot use the MS OLEDB Provider for DB2, because it works only in Enterprize Edition and we only have the Standard Edition.


Thus, only using Client Access ODBC Driver for DB2 with net providersodbc (as bridge) is performant enough and works on Itanium. But how to work around the problem with the special characters?


Best regards,
Stefoon

View 3 Replies View Related

Integration Services :: SSIS - Extracting From Excel Begin At A Row Include A Special Character?

Sep 18, 2015

I want to import date from excel file  from the row if this row has a character like 'TableName', and I don't know the rownumber and end column number ,so I can't use openrowset 'select * from Sheet$ A11:N'.   And I am thing if I can get the rownumber of the row contain  string 'TableName' and the max column ,that May be easy to extract data.

View 2 Replies View Related

T-SQL (SS2K8) :: Find String Before Character When Character Appears Multiple Times

May 17, 2015

I have a table that contains file paths as

ServernamefolderAfilenameA
ServernameFolderBFilenameB

and I need a query to return

ServernamefolderA
ServernameFolderB

I tried

SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table

But that removes everything after the first and I need it to return all data before the last

View 5 Replies View Related

T-SQL (SS2K8) :: Replace Multiple Occurrences Of Same Character With Single Character

Aug 6, 2015

I have the following scenario, The contents of main file are like :

ServerCentral|||||forum|||||||||||||||is||||||the||best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it

And I need the output in the following form:

ServerCentral=forum=is=the=best
so=be=on=it

The logic being that multiple and consecutive occurrences of the special character, here - pipe , should be replaced by a single special character.

View 5 Replies View Related

Select Part Of Character String Based On A Character

Apr 15, 2004

I have data in a column that starts with 1-4 characters followed by a dash then followed by an number of characters (ex: EU-Surgery).

How do I select everything to the right of the dash when the number of characters to the left of the dash varies?

View 3 Replies View Related

SQL Server Replacement

Sep 23, 2004

I've got a SQL Server hardware Replacement to do. This server has a merge replication setup on it which was setup by a contractor.

Do i have to do the configurations manually when moving the replication bits across two servers or is there a different process involved ? I am just not sure how to approach this.

View 1 Replies View Related

Text Replacement

Feb 28, 2008

Hello all,
I get some dates entered manually in french format in a varchar(255) field and want to convert that field in datetime. Since, Enterprise manager is warning me about a risk to loose information, I'm thinking about a way to update the french text in the corresponding english text before doing the conversion.
Example:
25-fév-08 (varchar(255)) has to become 25-feb-08 (datetime)

Could anybody help me, please?

View 2 Replies View Related

RSS Replacement For Sp_makewebtask

May 7, 2007

Hello All!

I have a job that we wrote in SQL 2000 that loops through and finds all the orders pending verification and distributes them via emal to 100+ sales people every night. Each email is sent to the individual sales rep. I did this using sp_makewebtask and attaching the html file to an email and using a WHILE statement to loop thorugh all pending entries in sql.

I recently learned that sp_makewebtask is not going to be around for the next release and that I should use RSS for all future development.

Question: How can I achieve the same result outlined above using RS? How can I dynamically create and distribute HTML files?

I know that RS is xml based, but some pointer on how/where to start would be much appreciated.

Many Thanks....

View 7 Replies View Related

REPLACEMENT PROBLEM

Feb 24, 2008

I wish to replace column2 'ABC' value become blank.

EXAMPLE TABLE VIEW

column2
-------
ABC 123, Alt. ABC 456, Alt ABC 789

select replace(replace(B.[column 2],A.[column 1],''),'ABC','')
from TABLE A,TABLE B
where A.id = B.id

After executed

column2
-------
123, Alt. 456, Alt. 789

But not expected result, because i jz wan ABC become blank not Alt. ABC.

So what should i modify with my logic and script?

View 19 Replies View Related

Replacement For My LIKE Clause

Dec 27, 2006

Dear all,I need some help from all my Transact SQL Guru friends out there..Here is the scenario in its most simplified form.. ..I have two tables.. A(Lookup table) and B(Transaction Table)TableA FieldsEmployeeLocationIDEmployeeLocation (This could have values say"B","BO","BOM","C","CA","CALC") etc...TableB FieldsEmployeeIDEmployeeName.......EmployeeLocationID (will have null initially when rows are populatedfirst time)EmployeeLocation (This could have values"BA123","BOMBAY","BOTS123","BRACK".... etc)I hope you get where I am leading this to, from my examples..Requirement is to populate the EmployeeLocationID in Table B withEmployeeLocationID from TableA by matching the field EmployeeLocationin both tables.Please note that table B's EmployeeLocation could be A'sEmployeeLocation + some additionalcodes like "123","RACK" etc in theabove example...Therefore, this is what I had wrote initially..update Bset B.EmployeeLocationID =A.EmployeeLocationID


Quote:

View 3 Replies View Related

String Replacement

Jul 20, 2005

Help, I have a vendor file with the vendors name. I need to add afield that has alternate captionseg. :In my vendor file I have records for AT&T with three differentnames/spellings for AT&T.AT&TLucent TechnologiesA T & TI want to add a new field that would have AT&T for the above records.I know I could hardcode a queryUpdate F_VENDORSet VENDOR_CAPTION_ALT = 'AT&T'where VENDOR_CAPTION like '%Lucent%' orVENDOR_CAPTION like '%A T & T%'however, how can I use another table that has the my search keywordsfor the "like" and the replacement captions so I dont need literalqueries to update all the diffent vendors?I have identified 96 instances of where I want to have an alternativename that I can use for a type of grouping. This gets really wildwith the military such as Army and Navy.TIARob

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved