Hi,
I come from the "dark side" php/mysql and there often problems with
character 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 to
encode (like base64) the strings?
Perhaps there are some links to read?
Thanks.
klaus.
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.
Hi,I have a MSSQL Server communicating with an Oracle database through aMSSQL linked server using a MS ODBC connection.If I query the Oracle database through the Oracle ODBC 32Bit Test, theresult is fine:select addrsurname from address where addrnr = 6666;HÅKANSSONIf I do the same query within the SQL Query Analyzer (using the linkedserver), I get:select * from openquery(TESTSW, 'select addrsurname from address whereaddrnr = 6666');H?KANSSONI have tried to both check and uncheck the Automatic ANSI to OEMconversion, but the result remains the same.Does anyone know what to do to make the result display the specialcharacters in SQL Query Analyzer?Thanks,Kenneth
View 4 Replies View RelatedHi,
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.
Hello!My client has a need to be able to store Japanese characters in theirPeopleSoft database. So we need to change the character set from fromLatin1_General (1252) to Japanese character set (932) on SQL Server2000 Enterprise. I have 2 questions:1) I would like to know if in SQL Server, the character set is machinespecific or is it defined at the database instance level?2) Can multiple installs of SQL Server co-exist on a single server withdifferent default character sets?ThanksVishal
View 1 Replies View RelatedI'm testing, with SQL 2014 on the same DB, a procedure that extracts data from a table into a file and Loads data from that file into a different table which has the same columns as the initial table (I use a function to create the create table statement from the source table and change the name of the destination table)
when doing my bcp -c the record with the special character "é" doesn't make it to the file.
when doing a bcp -w the record with the special character makes it to the file but the bulk insert omits the whole record.
The result file in the case that makes it to the file is unicode. I'm not using a format file (don't see the need for it)
The bulk insert into the destination table that contains identical columns as the source (a mixture on int, varchar, char) --> didn't work
I also tried by building the columns of the destination table with nvarchars -- Still doesn't work.
I tried the bulk insert with:
codepage = 'ACP, 'RAW' --> didn't change anything. still didn't work.
It's a complicated process that takes 1 XML record that contains information + the Create Table Statement (to eventually be able to this on a different server/DB) + the title Row for each column + the Data... Each of these are created with a BCP command (all with the same options). they are then appended to each other with a copy /B c:file1.txt + c:File2.txt + c:File3.txt + c:File4.csv c:ResultFile
Once the result file is created I bulk insert the 2 first rows in one table "TableA"
create the tmp table "TABLE B" with the create table statement that is in "TableA"
and do another bulk insert of the remainder of the file into the newly created table.
What else can I try? Should I be creating a format file? what are the benefits of a format file?
It's a very long procedure that does both Extract and Load (with 12 parameters) not sure what I should put here.
I'm trying to replace special characters in SQL SERVER and all the solutions for this RDBMS that I found, it uses loops and the source of my data it's in Oracle. in ORACLE and they use REGULAR EXPRESIONS to solve it..Do you know what its the better option to replace special characters? Using loops in SQL SERVER or REGULAR EXPRESSIONS in ORACLE ?
View 5 Replies View RelatedHow 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
I need to find all uses of special characters in a database. I used the following code to do this:
USE dbName
GO
IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE #Results
GO
[code]...
This will check all tables in the database, but if you want to check specific tables you can uncomment the line in the where clause and specify tables to be checked. The query will return any text fields that have any characters other than letters, numbers or spaces.
This code works fine for me because all the tables in my database have single column primary keys. However I know how much Jeff Moden hates cursors or RBAR queries, so my question is could this have been done by any method other than using a cursor?
Part 1: When there is ~ (tilde) and has any value after it then it goes into a new row and duplicating the other columns like the facility in the screenshot attached and new column having the sequence.
Part 2: When there is ^ (Caret) its a new column irrespective of a value present or not
CREATE TABLE [dbo].[Equipment](
[EQU] [VARCHAR](50) NOT NULL,
[Notes] [TEXT] NULL,
[Facility] [VARCHAR](50) NULL)
INSERT INTO [dbo].[Equipment] ([EQU] ,[Notes] ,[Facility])
SELECT '1001','BET I^BOBBETT,DAN^1.0^REGULAR^22.09^22.090~BET II^^^REGULAR^23.56^0~','USA' union
SELECT '998','BET I^JONES, ALANA^0.50^REGULAR^22.09^11.0450~BET II^^^REGULAR^23.56^0~','Canada' UNION
select '55','BET I^SLADE,ADAM F.^1.5^REGULAR^27.65^41.475~','USA'
SELECT * FROM dbo.Equipment
I created the table in excel and attached the screenshot for a clear picture as to what is required. I use text to Columns in excel to achieve this not sure if there is anything similar in sql.
I'm presented with a problem where I have a database table which must be migrated via a "custom tool", moving the data into a new table which has special character requirements that didn't exist in the source database. My data resides in an SQL Server 2008R2 instance.
I envision a one-time query which will loop through selected records and replace the offending characters with --, however I'm having trouble understanding how this works.
There are roughly 2500 records which meet the criteria of "contains bad characters", frequently containing multiple separate bad chars, and the table contains roughly 100000 rows.
Special Characters are defined as #%&*:<>?/{}|~ and ..
While the field is called "Filename" it isn't always so, it is a parent/child table where foldernames are also stored.
Example data:
Tablename = Items
ItemID Filename ListID
1 Badfile<2015>.docx 15
2 Goodfile.docx 15
3 MoreBad#.docx 15
4 Dog&Cat#17.pdf 15
5 John's "Special" Folder 16
The examples I'm finding are all oriented around SELECT statements, to change the output of what I see returned, however I'd rather just fix the entire column using an UPDATE. Initial testing using REPLACE fails because I don't always have a single character as the bad thing in a string.
In a better solution, I found an example using a User Defined Function to modify the output of a select, but I cannot use that UDF in an UPDATE.
My alternative is to learn enough C# to modify the "migration tool" to do this in-transit, but I know even less about C# than I do of SQL.
I gather I want to use @@ROWCOUNT to loop through the rows but I really can't put it all together in a cohesive way.
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 RelatedHi All
I have a database I want to check whether any special characters are entered or not
E.g.
Post
-------
Director
Asst*Director
Ma^nager
I want to see all the entires where special characters like !@#$%^&*()_+ are entered
Plz help
Thanks
Hi,
I an concatenating a string using char(187) as the separator. When I run the command "select char(187)" I get the symbol >>
However, when it writes to a csv file it is showing as an overscore instead.
Is there a known problem with special characters when writing to files?
Our SQL 2005 databases have "SQL_Latin1_General_CP1_CI_AS"
collation settings on the server. One of tables has one column declared as "ntext".
When we try to insert "‚¬" character directly trough insert statement and retrieve it, we're getting right results. When we use web-services to insert data with this character (euro), and retrieving the data back, we're not getting this sign back. We are getting some characters like little squares. If I try to copy and paste them into notepad, I am getting ???
However, when I tried to place them here, I got them right:
‚¬ Å Å¡ Ž ž Å’ Å“ Ÿ
How can we assure visually they are inserted properly?
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.
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?
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"
I have a need to store records in two different languages. Has anyone done this and what were the requirements. Can you do this within the same database, table?
Thanks for all feedback.
On the bills that our system generates there is a comments field that users fillout. We have occasional problems with special characters in the text messing up the validation code. Does anyone know of a query that can identify special characters in a text field? Like carriage returns, tabs, etc.?
Thanks, Dave
Iam working on sql server 7.0.
I have to bring in data from a text file into the sql server database. I use the BCP utility in pulling the data.
The flat file data contains special characters like ®. This is the registered trade mark symbol. When I bcp the file using the character mode the special characters are automatically changed to «. So If I am going to pull a data like ACRYLON®, after the BCP I get it as ACRYLON«.
Inorder to replace all these I have written a program but since it has to scan every record the time taken is more. Will in be possible to accomodate iun the BCP to pull the data with special characters.
If you have an answer to this please reply soon.
Thanks,
-Rajesh
Hi !
I have an DB in SQL Server 7, and in Portuguese we have special characters like "á","õ",etc. And I want to let the visitor to a site to do a search (written in ASP), and not to need to write the correct way (without the accents). But either he writes or not in the correct way, the results are the same, not necessarly in the same order. Is there a SQL Server mechanism that permits this functionality, without doing a very complicated SELECT (takes a lot of time) or replicating a field in the DB (takes a lot of extra space)...
Thanks !
Filipe Lopes, Lisbon, Portugal.
I have a problem inserting German special chracters into a MSSql table.
üöäÖ works fine, but ßÜÄ are reduced to "_". I'm using a html form and a php script to enter the data into the database. Any idea why this isn't working? Funny thing is, when I use a german version of the SQL Server it also works without a problem... but I need this to work on ANY mssql server. Any help would be very much appreciated.
Michael
Hi, I'm new here. Happy new year!
Simple question. I can not insert special characters such as ' " ( ) etc. What special escape I need to put in my sql statement?
Hi,This is a generic question, but for arguement's sake, let's say, myenvironment is SQL Server 2000.It seems that setting quoted_identifier off is the best way toaccomodate all sort of data input/update especially for data set thatcontains special characters like single quote as in O'Brien, otherfunky stuff like %^$*@#(!).However, this option won't help with a situation like data value of5'9",and let's say, we really can't predicate if this type of value will beused for ColA or ColB or ColC ...Examplecreate table #tmp (col1 varchar(30));set quoted_identifier off;insert into #tmp values("O'Brien's barking dog'");-- successinsert into #tmp values("O'book has funky (%^$*7) stuff");-- successinsert into #tmp values("John said "funky is OK" in his speech");-- failedGlobaly search and replace the double quotes before insert/update isjust too "expensive", not a good option.Now, if we can set a custom quoted_identifier, we can solve the aboveproblem easily, for instance,if MS SQL Server permits/acceptsset quoted_identier = '//$$';theninsert into #tmp values(//$$John said "funky is OK" in hisspeech//$$);(or the like)would succeedAny other thoughts/ideas? Thanks.Quote "Never stop thinking even though at times it may produce waste,which we all do, btw :) "
View 4 Replies View RelatedCurrently I have got a report where I want to insert special characters (like @, !, ?) at the end of a String which will be displayed in a textbox of the report. Wenn I insert the String value "test!?" the report will return this value "!?test".
Does somebody know what is going wrong?
Good day
I've looked for information about this but to no avail. Essentially why does Reporting Services not support spaces and special characters in a Field Name?
I know that SQL supports spaces utilizing [] brackets e.g. [Field Name With Spaces]. So how come RS doesn't?
Thanks
I am trying to display data that has trademark and copyright symbols. Reporting services does not display them in the standard format. Anyhelp is appreciated on how to get this done.
View 3 Replies View RelatedI have a problem where I want to write a function to remove recurring characters from a string and replace them with a single same character.
For instance I have the string '12333345566689' and the result should be '12345689'. In Oracle I could do this with "regexp_replace('12333345566689', '(.)1+', '1')", but in T-SQL the only solution I could think of is something like this:
DECLARE @code NVARCHAR(255)
SET @code = '12333345566689';
SET @code = REPLACE(REPLACE(REPLACE(@Code, '1', '~1'), '1~', ''), '~1', '1');
and repeat this for 2 - 9. But I'm sure there is a more elegant version for this in SQL Server 2012.
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.