I have a table and one of the column have junk characters in it, how can I remove the junk characters?.
Eg : Employee
Eid Ename
1 a�
2 �ddd
how can i remove Junk characters and get only Enames from above table.
I have a table called exchange and field called address. The rows(1400+) in the field look like:
MS:VA/Celcmv/VHACLEADAM%SMTP:Doe.Jane@med.va.gov%X200:c=US;a= ;p=av;o=Celcmv;s=Doe;g=Jane;
How do I remove everything to the left of doe.jane@med.va.gov and everything to the right of doe.jane@med.va.gov using query analyzer? Thank you in advance...
Can someone please suggest a function to remove the last 3 characters from a column? I was thinking of the LEN function, but I am unsure of the syntax.
Thanks!
Lisa
Hi,
I am Suhasini. While saving data from front end(Asp.net) to back end(Sql server 2005 express edition) i am getting junk characters also added to the database. This character just look like a checkbox. Basically i am adding options using a multiline text box, is there any thing wrong with that. options are saved in the database as junk character followed by option1...... etc. Kindly suggest me on this.
How to find and replace the Junk Characters which is accidently stored in the "TEXT" data type column of one table.
View 5 Replies View RelatedHi
We are generating the log file using SSIS built in logging feature. It is generating a text file as "log.txt".
Again we need to add few string values like "Files Loaded Successfully". So i tried adding this string using the following 2 ways.
1. Using Script Task in SSIS
2. Using SQL Stored procedure.
The following are the code:
Through SSIS Script:
Contents = "File Loaded Successfully"
'Get a StreamReader class that can be used to read the file
Dim objStreamWriter As IO.StreamWriter
objStreamWriter =
IO.File.AppendText("\Chnbslsql2k3shareLoglog.txt")
objStreamWriter.Write(Contents)
'Close the stream
objStreamWriter.Close()
Through Stored Proc:
CREATE PROCEDURE [dbo].[LG_Create_LogFile]
AS
BEGIN
SET NOCOUNT ON
DECLARE
@cmdstr as nvarchar(1000),
@Msg AS varchar(1000)
BEGIN TRY
BEGIN
-- Create move command
set @Msg = 'File Loaded by Kumaran '
set @cmdstr = 'echo ' + @Msg + ' >>
\Chnbslsql2k3shareLoglog.txt'
END
-- Execute command
EXEC master..xp_cmdshell @cmdstr
END TRY
BEGIN CATCH
RAISERROR('FILE NOT MOVED SUCCESSFULL', 16, -1)
RETURN -1
END CATCH
END
In the both ways, it is giving me junk characters as like in the below output log.txt file...
PackageStart,CHNBSLSQL2K3,NT AUTHORITYSYSTEM,pkgExtract,{93BE582E-EDDD-4EBD-97A4-8A2E39E839A9},{605B2ED1-D794-4455-BD9E-F32D4CFF1196},9/18/2006 8:43:40 PM,9/18/2006 8:43:40 PM,0,0x,Beginning of package execution.
OnError,,,pkgExtract,,,9/18/2006 8:43:43 PM,9/18/2006 8:43:43 PM,-1071636466,0x,Cannot open the datafile "\Chnbslsql2k3shareRawCPSExtract.dat".
PackageEnd,CHNBSLSQL2K3,NT AUTHORITYSYSTEM,pkgExtract,{93BE582E-EDDD-4EBD-97A4-8A2E39E839A9},{605B2ED1-D794-4455-BD9E-F32D4CFF1196},9/18/2006 8:43:48 PM,9/18/2006 8:43:48 PM,1,0x,End of package execution.
æ¥†æ•¬ä° æ…¯æ•¤?¤ç¥¢ä¬ 浵牡湡€ à¨?
This is a issue which i am struggling and looking on..Please help me out with some gud
solutions.
Thanks
Kumaran
Hai all,
We have quite a strange problem, we have some SP's in our project which is functioning quite well all these days. Suddenly one day we got an error in the SP stating "Invalid Table Name", when we opened the SP and saw in some places the Tablename was replaced with Junk Characters inside the SP !!!!!!!
For Ex: Inside the SP we have the following SELECT query
"SELECT F1 FROM SampleTable"
the above query is getting replaced with
"SELECT F1 FROM SampleTa?le"
The junk character actually appears like a box!!!.. we were absolutely clueless why this was happening. Then if we change the SP once agin and run it starts to execute but only for a short time and the problem comes back once again. When we went through the SQL logs we got this error repeatedly:
"Error: 17805, Severity: 20, State: 3
Invalid buffer received from clients"
We came to know a little about this problem from kb articles in MS that when you are calling an SP from .Net u should specify the parameter data types explicitly and u should not use SQL Client in Finalise method all which we have not done in the application.
Tha SP was using #Table which we replaced to @Tablevariables but still we got the problem once, we are monitoring with the same Table Variables.
We are using SQL Server 2000 SP4. We have got stuck with this problem for days now . Any help is greatly appreciated.
Thanks in advance.
Back Story...
i am building a new deployment routine for one of our products using NANT (.net version of ANT). I takes all of .sql files out of vault for all non table objects and concatenates them into larger .sql files with IF EXISTS .... DROP ... GO CREATE statements which will then be fired off by osql batch files that will log the errors.
Problem...
When I open the resulting files in notepad I have junk characters (microsoft OEM stuff) in some of my object definitions which you do not see in the QA or if you opnen the file in Vault. I need to get this stuff out of there and I do not want to write a bunch of filesystem code to do it because I need this tested and ready by Friday COB. I know I can solve this by opening the problematic files in ultraedit and saving them one by one but I do have that kind of time.
Any ideas?
It's going to be a long couple of days.
Hi All,I am using command line bcp utility of SQL Server to import data from a textfile to database.I have some german words in the text file and afterimport the German characters are lost.see eg below.Input : Kühner, Klaus -> Text file valueOutPut: K?hner, Klaus -> Table data, after import.I am using unicode in the commad line.The collation setting is LATINcan anyone show some light.Thanks in advanceManoj.
View 1 Replies View RelatedI have a strange situation in an Message queue task in SSIS.
I serialize an object in a C# application and add that to an MSMQ as a string. I also ensure that I set the label to "String Message" so that my Message Queue Task can actually receive the message as a String message to variable.
I created an SSIS package that has an Message Queue listener that feeds into a Script task inside a for-each loop.
For each message that I obtain, I invoke a script task that retrieves the value of the variable and then processes this information.
When I enter the entry into the MSMQ, it goes in perfectly fine (since I also tested retrieving this entry from a C# app). However when I use the same logic on the SSIS package using the Script task, I get junk chinese characters.
Has this happened to anyone else?
Any feedback would be great!
Anup
Here is the code for the script task:
mports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim statusMessage As String
statusMessage = CType(ReadVariable("ReviewerHealthXmlMessage"), String)
System.Windows.Forms.MessageBox.Show(statusMessage)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
End Class
I’m getting ASCII characters in one column of my table. So I want to replace same column value in NON ASCII characters.
Note – values in column must be same
I need an SQL statement that will hide a field in a column without removing it from the table.
View 3 Replies View RelatedHello all,
I have a table named users. It consists of user names, user ids, etc... The problem is that whoever designed the ASP code before me allowed people to enter info in any format they want. This poses a problem because now the name can have 1, 2, or sometimes 3 spaces in between the last and first names. And sometimes the middle initial is used with a period following it. This creates problems when I am trying to execute a Select statement since it won't match if there are an unknown number of spaces in the string and throws an error when a period is used.
Is there a SQL query I can execute to change all the user_names into a format such as the following:
LastName, FirstName MiddleInitial
Like I said, it is already almost the same, just has too many spaces and some have periods after the middle initial
I've look at several different methods for removing leading zero's from a column but I need to remove trailing data from a VARCHAR column. For some reason, the old database saved the time along side the date in my client's app.
For example:
The old database format "2015-07-28 00:00:00"
I need the data in this column in the new database to only be the date "2015-07-28", there are alot of rows with this issue.
Is there a query I can run to remove the 00-00-00 from all of the rows? Some of the fields actually have a time in there like this: 2015-07-28 12:15:35, with this one, I don't think it's going to be easy but if I could at least remove the 00-00-00 from all the rows that have it, that would be a good start.
I need write a query for removing duplicates, for Example in my table I have columns
A_ID name id
1 sam 10
2 sam 10
3 sam 10
4 sam 10
5 ccc 15
6 ccc 15
7 ccc 15
8 fff 20
9 fff 20
10 fff 20
So now I have duplicates values in id column so now I need to take only one value of each and delete the remaining. I need to take first id value 10,15,20 so only 3 rows should be there in my table.
I've got one SQL Server 7.0 table with a "Decsciption" Column of length 4000. The values in this column contains "End of Line" ASCII Character. The ASCII Value of this character is 10. I'm not able to remove this ASCII Character. I tried by using REPLACE function. But i could not remove that character.
Any thoughts are welcome,
Regards,
Santha.
Folks ... I have a pipe-delimited ASCII text file with a lot ofdifferent non-printing characters. Rather than try and figure out allthe non-printing characters that exist in this 17+ million recorddatabase, I was hoping someone might have already written a scriptthey'd be willing to share that would remove all non-printingcharacters from an ASCII file?Thanks,Ralph NobleJoin Bytes!
View 1 Replies View Related
here is my data
112344*100
23*34444
I want to remove * and make sure the length is 11 digits and and add leading 0's
Desired data
00112344100
00002334444
How will i be able to achieve this..
Hello,
I am using the following expression to strip the last 11 characters in a field group and it returns an error saying that Len cannot use a negative number. It must be 0 or greater. Is there a better alternative?
Code Snippet
left(Fields!TestName.Value,len(Fields!TestName.Value)-11))
Hi,
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
I have a table with several columns of information that I wish to set up some form of schedule to go through this data and remove any special characters that may interfere with other code processes.
Mainly the coma's and the apostrophes. It really messes with my asp pages and scripts when retrieving this information and trying to do other things with it, so I need to figure out how to remove these from the tables so it does not cause these issues.
Knowing this, I cannot figure out how to keep the data in the row/column and just extract the special characters from that data. The other problem is, everything I try requires me to insert either a coma or apostrophe as part of the code string which in lies my issue.
How can I parse through my data, leave the data as-is, but just get rid of coma's, apostrophes, and double quotes?
Does anyone have a basic example that I can use to expand on?
Hi I am trying to strip out any non-alpha characters from a field.
i.e. Field = ABC"_IT8*$ should return: ABCIT8
I am writing a loop to do this for all values of a field. The script runs, but hangs....please could somebody advise on the code below...:
I run the script but it doesn't seem to finish. Can anybody see any issues with the code:
DECLARE @Index SMALLINT,
@MATCH_Supplier_name varchar(500),
@Counter numeric,
@Max numeric
-- @sqlstring varchar(500)
SET @Counter = 1
SET @Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)
WHILE @Counter <@Max
BEGIN
SET @MATCH_Supplier_name = (SELECT Match_Supplier_Name FROM SUPPLIER_TABLE_TEST WHERE @Counter = DTect_Supplier_SRN)
SET @Index = LEN(@MATCH_Supplier_name)
WHILE @Index > = 1
SET @MATCH_Supplier_name = CASE
WHEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) LIKE '[a-zA-Z]' TH EN SUBSTRING(@MATCH_Supplier_name, @Index, 1)
WHEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) LIKE '[0-9]' THEN SUBSTRING(@MATCH_Supplier_name, @Index, 1)
ELSE ''
END + @MATCH_Supplier_name
SET @Index = @Index - 1
--PRINT @MATCH_Supplier_name
SET @Counter = @Counter + 1
END
I usually do this through Access so I'm not too familiar with the string functions in SQL. My question is, how do you remove characters from the middle of a string?
Ex:
String value is 10 characters long.
The string value is X000001250.
The end result should look like, X1250.
I've tried mixing/matching multiple string functions with no success. The only solution I have come up with removes ALL of the zeros, including the tailing zero. The goal is to only remove the consecutive zeroes in the middle of the string.
I have a varchar field which contains some Greek characters (α, β, γ, etc...) among the regular Latin characters. I need to replace these characters with a word (alpha, beta, gamma etc...). When I try to do this, I find that it is also replacing some of the Latin characters.
DECLARE @Letters TABLE (Letter NVARCHAR(10))
INSERT INTO @Letters VALUES ('a'), ('A'), ('b'), ('B'), ('α')
SELECTLetter, REPLACE(Letter,'α','alpha')
FROM@Letters
In this case, the "α" is being replaced, but so are "a" and "A".
I have tried changing the datatype from varchar to nvarchar and also changing the collation.
I 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].
I have to select rows from a table
if the first 2 characters of a 12 char column are
'GB'
Select BFKEYC from table where
I have a hokey way of doing it but it looks embarrassing:
BFKEYC GT 'GA9999999999'
AND BFKEYC LT 'GC'
In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message.
How can I store over 16000 characters to sql table field (only one field) with language specific characters?
Thanks
Hi,
I have one column in which i have Alpha-numeric data like
COLUMN X
-----------------------
+91 (876) 098 6789
1-567-987-7655
.
.
.
.
so on.
I want to remove Non-numeric characters from above (space,'(',')',+,........)
i want to write something generic (suppose some function to which i pass the column)
thanks in advance,
Mandip
How to remove identity property from a column throught SQL statement?
View 4 Replies View RelatedHi all,
I have one table in which one column contains duplicate values. My question is how i can use T-SQL so that i can retrive values for all columns in the table which are distinct and retriving the single value from column which contains duplicate values.
(I know distinct)
Thanks in advance.
Minesh.
Hi all
i want to remove text from my column name using query.
for example
i have the product name like "silver 8' trampoline pack "
i need to remove "silver 8' " and want to display only trampoline pack
similarly if I have product name like "gold 8' trampoline pack"
i need to display only trampoline pack.
can anybody help me in this regard?
thanks
We can easily remove identity columnn through enterprise manager but how can it be done through transact sql?
The only way i found is to create a new column and pass values if identity column in it and then remove this identity column is there any better method of doing it?
Hi,
I have the following tables :
Code Block
Create table #EmployeeList(empname nvarchar(20), emptype char(5))
Insert INTO #EmployeeList('Cary zzz',null);
Insert INTO #EmployeeList('01 Jack',null);
Insert INTO #EmployeeList('02 Tommy',null);
Insert INTO #EmployeeList('03 Ricardo',null);
Insert INTO #EmployeeList('04 Jack',null);
Insert INTO #EmployeeList('Les zzz',null);
Insert INTO #EmployeeList('05 Tim',null);
The final data looks like this :
Cary zzz NULL
01 Jack NULL
02 Tommy NULL
03 Ricardo NULL
04 Jack NULL
Les zzz NULL
05 Tim NULL
1. I want to delete all rows which have 'zzz' in it.
2. I want to remove the numbers from the empname column
Code Block
Expected Output :
Jack NULL
Tommy NULL
Ricardo NULL
Jack NULL
Tim NULL
Can anyone help me please with the query?
thanks.