T-SQL (SS2K8) :: How To Remove Special Characters From Column Value In A String
May 16, 2015I want to remove special characters from a string in sql like <?> in a column value in a table.
View 1 Replies
ADVERTISEMENT
SQL Server 2008 :: Normalizing Data Prior To Migration (Update String To Remove Special Characters)
Aug 21, 2015I'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.
T-SQL (SS2K8) :: Search Special Characters In Column Of Table
Jul 14, 2014I am using SQL Server 2008. In one of my table, one column has values like
"MFY RLHH CSQÉ"
"Aamj Gxmolwn Slf Yytrzgan Hiwd Fnlmyw"
So to fetch the data having only special characters in it, I used below query
Select * From Table Where Column Like '%[^0-9a-zA-Z]%' Escape ' '. Its returning both the records. Here I would like to fetch records for those Unicode characters only which are not within 00201 - 0070E [URL].
Removing Special Characters From A Number String.
Jul 11, 2001Hi,
I was wondering what would be the best way to remove special characters like, '-', '&' '(',')','#','*', etc... from a number string. To be specific a phone Number string where the string is >= 10.
Thanks, Mark
T-SQL (SS2K8) :: Remove All Characters After 3rd Repetition Of A Character?
Sep 19, 2014best possible way to remove all the characters after a 3rd repetition of a character?
For Example:
I want 10.0.1600.22 to be 10.0.1600
Everything after and including the '.' to be removed.
I understand Substring_Index() is not available whats the other options?
SQL Server 2008 :: String Break Into First Rows And Then Columns Based On Special Characters?
Jul 1, 2015Part 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.
How To Remove Characters In String
Jun 20, 2008There are unwanted characters(''','/','&'.. etc) in column.
I need to remove these characters
How To Remove Non-Numeric Or Non-alphameric Characters From A String In Sql Server 2005
May 16, 2007Hi to all,
I am having a string like (234) 522-4342.
i have to remove the non numeric characters from the above string.
Please help me in this regards.
Thanks in advance.
M.ArulMani
How To Remove Non-Numeric Or Non-alphameric Characters From A String In Sql Server 2005
May 16, 2007Hi to all,
I am having a string like (234) 522-4342.
i have to remove the non numeric characters from the above string.
Please help me in this regards.
Thanks in advance.
M.ArulMani
T-SQL (SS2K8) :: Find Occurrence And Remove String?
Mar 19, 2014My sample data is as below.
plan type A change from plan type B from Plan type C
Insurance plan M changed from Insurance plan b From plan d from Plan N
Now from above strings i want to remove all data from second appearance of from. i.e. i want display values as below.
plan type A change from plan type B
Insurance plan M changed from Insurance plan b
SQL Server 2012 :: Select Case Statement To Remove Part Of String After One Or Two Specific Characters
Jun 3, 2015I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN***
Example Record 2. Theale, Nr Reading, Berkshire
Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0
THEN LEN(Address )
ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0
THEN LEN(Address)
ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
T-SQL (SS2K8) :: Trying To Use Substring (charindex) To Remove Start Of A String
Aug 26, 2014Looking at a trace table and trying to remove all the "erroneous" bits in a string e.g the declares etc so I can purely get to proc names.
An example string
declare @p2 varchar(10) set @p2=NULL exec sp_proc @Code='TF',@TypeCode=@p2 output select @p2
I've tried
select top 5000 textdata,substring(textdata,charindex('exec',textdata)+5,charindex('@',textdata)-1)
from trace_table
where TextData like '%sp_%'
and TextData like '%declare%'
And it fails dismally...
T-SQL (SS2K8) :: Remove Non-numeric Values From A Nvarchar Column
Dec 9, 2014We have an address table with a house_num field which is a nvarchar.
Most of house numbers are numbers like 1234, 0989
But some of them have a letter behind it like 678 B, 8909 F, even some like this 123/B
We would like to remove any non-numeric letter for this column.
Is there a way to do it?
T-SQL (SS2K8) :: Replace Multiple Characters With Single Character In A Column?
Jun 21, 2012I am trying to replace all special characters in a column with one special character.
Example:
Table: dbo.Employee
Column: Name
Name
-------
edwardneuman!"<]
mikemoreno)'>$:
JeffJensen"?>"
I am trying to get the namepart to the left of ANY special character. To achieve this, I am thinking of replacing all the special characters with a single special character so that I can find the first occurrence of that special character and grab left of the special character (SUBSTRING/CHARINDEX). This way I don't need to loop through all the special characters.
I am expecting the following results:
Name
-------
edwardneuman<<<<
mikemoreno<<<<<
JeffJensen<<<<
Return Only Numeric Characters From A String Column
Dec 4, 2007I have written this query to return only numeric characters from a string.
select *
from TableA
where isNumeric(Column_A) = 1
But I have discovered that the following
SELECT IsNumeric('-')
SELECT IsNumeric('£')
SELECT IsNumeric('$')
SELECT IsNumeric('+')
all return a value of 1. I do not want these in my result set.
If I use Column_A NOT LIKE '%[a-z]%' and Column_A <> '' I get characters such as "", ----- etc in my result set.
I would like a simple way of only returning numbers in my resultset.
Thanks for looking at this.
Special Characters
Jan 16, 2006Hi 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
Special Characters
Mar 10, 2008Hi,
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?
Special Characters
Apr 2, 2008 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?
SQL Server 2012 :: Remove String From Column Post Code Field
Sep 8, 2015I am querying with a SELECT statement against an address table that has a post code column with corrupt data.
Sample record:- Northants NN4 0NB
Should be NN4 0NB
I have used the following on another column:-
LEFT(A.Addr1,CASE WHEN CHARINDEX ('(', A.Addr1) =0 THEN LEN(A.Addr1) ELSE CHARINDEX('(' ,A.Addr1) -1 END) AS 'Address 1'
but unable to correct this problem?
SQL Query For Special Characters
Apr 18, 2007On 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
BCP Issue-Special Characters
Jul 6, 2000Iam 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
Special Characters - Search In DB
Mar 6, 2001Hi !
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.
German Special Characters
Apr 13, 2003I 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
Can't Insert Special Characters
Jan 2, 2007Hi, 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?
Special Characters In MSSQL.
Mar 7, 2006Hi,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 RelatedTreatment Of Special Characters
Jul 20, 2005Hi,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 RelatedSpecial Characters In TextBox
Jun 14, 2007Currently 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?
Spaces And Special Characters
Aug 31, 2007Good 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
Special Characters In Reports
Jan 5, 2007I 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 RelatedRemove Special Caracters
Feb 7, 2005how do i remove
in all of my fields in the table ? using a TSQL command ?
"DesktopModules/Events/Events.ascx
"
thanx !!
Storing Special Characters For Use In Email
Feb 19, 2003I am trying to store the content (body) of an email message that I want to create in a column SQL 2000. I need to know how I can store the special characters (carriage returns, bullets, etc) so that when I select them from the database to reform the email the correct formatting appears.
Any suggestions?
Finding Duplicates Regardless Of Special Characters - Help Plz
Nov 22, 2005I am trying to move distinct data from one table to another based on two columns regardless of special characters such as : ( ) ; ' " / - _ = >< etc. The two columns that are the defining factors that I need to match as duplicates are Col1 & Col3, the rest I want to get the maximum data from each column for that row (I hope this made sense).
Here is what I have tried, but it does not seem to work:
Code:
select
ID,
Col1,
max(Col2)as Col2,
Col3,
max(Col4)as Col4,
max(Col5)as Col5,
max(Col6)as Col6,
max(Col7)as Col7,
max(Col8)as Col8,
max(Col9)as Col9
where patindex('%[^-:]%',Col1) = 0 AND patindex('%[^-:]%',Col3) = 0
into Newtable
from dbo.CLEANING_bk
group by Col1,Col3
Basically if there are 10 rows of duplicate data based on Col1 & Col3 (regardless of special characters), I want to move the distinct info to a new table with the maximum amount of info from the other columns of the duplicate rows.
If row 3, 4,5, & 6 are all considered duplicate and Row3/Col2 has info in it that row 4 & 5/Col2 does not, I want to combine it with the single row I export to the new table retaining as much information from the rows of the duplicates as possible.
Can anyone help and let me know why my script is not wanting to play nice?
Insert Special Characters In Database
Sep 19, 2006I am writing a web application and everytime I attempt to insert some special characters in the database I get errors. Is there a way around this?
View 5 Replies View Related