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"
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
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 RelatedI 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.
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?
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.
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"
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 RelatedHello 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.
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.
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
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
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.
I would like to write a SQL to extract a text after certain _ special character.Example :Â Relationship_Person_Type_Aunt
View 9 Replies View RelatedThere 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 RelatedHi, 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!
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.
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 RelatedI 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
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
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
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
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
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 RelatedI 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
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.
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?
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.
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?
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....
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?
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:
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