Ascii Character Query
Mar 18, 2008
item custclass totalcustclass
-------------------------
06-5841 INST-CLINPRAC 1
06-5841 INST-MKT/MEDIA 2
06-5841 PROGRAM 1
06-5841 STANDARD 4
06-5845 STANDARD 1
AX-048 INST-MKT/MEDIA 4
KT-048 PROGRAM 2
KT-048 STANDARD 4
i want condition like if item is starting with number then totalcustclass whcih is count(*) remain same giving correct results...but if item startign with ascii character then totalcustclass r getting double so i have to /2..
i want results:
item custclass totalcustclass
-------------------------
06-5841 INST-CLINPRAC 1
06-5841 INST-MKT/MEDIA 2
06-5841 PROGRAM 1
06-5841 STANDARD 4
06-5845 STANDARD 1
AX-048 INST-MKT/MEDIA 2
KT-048 PROGRAM 1
KT-048 STANDARD 2
select
item, custclass,
case when item is <ascii> then count(custclass)/2
else count(custclass)
as totalcustclass
from itemcustclass
can anyone tell me what condition will come in case?
thanks for help.
View 2 Replies
ADVERTISEMENT
Feb 11, 2008
I have an issue with some data that has a leading ASCII char 160 (the "a" with the accent mark) but it shows in query results as a space.
... where customername like char(160) + '%'
returns 2 rows but shows the customer name with a leading space. How would I change the collation or do otherwise to get this character to display correctly in the results?
Thanks!
-Dave
View 6 Replies
View Related
Jun 19, 2006
Hi,
I'm working on a database conversion from Sybase to SQL Server 2005 and have hit a wall with a character conversion problem when reading non-ASCII characters (encrypted password string) via JDBC.
My application runs on Solaris and accesses a SQL Server 2005 database via the Microsoft JDBC driver. The server was unfortunately specified as having a SQL_Latin1_General_CP1_CI_AS collation at installation time, and the database being accessed has taken this default. After creation the data was migrated across via DTS.
The invalid character is a dagger '†'. When read over JDBC it is converted to a question mark '?'.
In my original environment a Sybase database was accessed via JDBC driver from Solaris and the correct value was returned. The Sybase database used Latin1_General_BIN as it's collation. By way of experimentation I have modified the default collation sequence within the SQL Server 2005 database, and created a new table to hold the password. I am then able to correctly return strings containing this character from within SQL Server Management Studio, but the same problem still exists when accessing it via JDBC.
I am not sure where to focus my investigation and would be grateful for any useful pointers/advice. To me it looks like it's a JDBC driver issue as with the change in collation it works from a non-JDBC client.
Many thanks
Alistair
View 2 Replies
View Related
Apr 30, 2007
hi all,
I have a stored procedure with a input parameter ( xml block).
sp: spGetAddr @xmlText
the style of parameter @xmlText looks like
<Param RestricBy="UserName IN ('Mayá', 'Bob')"/>
If I use non-basic ASCII character, just like 'á' in the parameter, the execution fails and I get 'The error description is 'An invalid character was found in text content.''
But this kind of special characters should be allowed in the name column of the database table.
If I use the htlm code 'á" to replace 'á' in the parameter, then the execution of the stored procedure is fine. but how can I change all of the possible sepcial characters?
If somebody knows how to handle this issue, please help me. I'm pre-appreciated for all the idea and suggestion!
Thanks,
Jone
View 4 Replies
View Related
Jun 19, 2006
Hi,
I'm working on a database conversion from Sybase to SQL Server 2005 and have hit a wall with a character conversion problem when reading non-ASCII characters (encrypted password) via JDBC.
My application runs on Solaris and accesses a SQL Server 2005 database via the Microsoft JDBC driver. The server was unfortunately specified as having a SQL_Latin1_General_CP1_CI_AS collation at installation time, and the database being accessed has taken this default. After creation the data was migrated across via DTS.
The invalid character is a dagger '€ '. When read over JDBC it is converted to a question mark '?'.
In my original environment a Sybase database was accessed via JDBC driver from Solaris and the correct value was returned. The Sybase database used Latin1_General_BIN as it's collation. By way of experimentation I have modified the default collation sequence within the SQL Server 2005 database, and created a new table to hold the password. I am then able to correctly return strings containing this character from within SQL Server Management Studio, but the same problem still exists when accessing it via JDBC.
I am not sure where to focus my investigation and would be grateful for any useful pointers/advice. To me it looks like it's a JDBC driver issue as with the change in collation it works from a non-JDBC client.
Many thanks
Alistair
View 6 Replies
View Related
Aug 22, 2002
Hello,
I am glad if someone help me. My question is like belo:
i have ascii data in one column name(failcode), i want to use it to link with other table which is the data type in integer. so i have to convert it into integer type.
May i know how to write in query to convert ascii to int in Sequence Server(Microsoft SQL Server 7.0)?
View 2 Replies
View Related
Nov 17, 1998
Need to convert a sql query recordset to an ASCII tab deliminated to import into an excel spreadsheet.
Are there any work arounds for this.
Jah Red
jahred@globalmusic.com
View 1 Replies
View Related
Oct 22, 2015
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
View 10 Replies
View Related
May 2, 2008
begin
SET @STR='INSERT INTO tbl_Level1' + '(' + @columnName + ',orgID' + ')' + 'Values' + '(' + @Level1Name + ',' + @orgID + ')'
EXEC @STR
ERROR :- Conversion failed when converting the varchar value 'INSERT INTO tbl_Level1 (Level1Name_HIN,orgID ) Values (@Level1Name,' to data type int. pls help me out
Yaman
View 10 Replies
View Related
Feb 28, 2002
Can I use a newline character in my query? I want to dynamically generate a statement, split on several lines. Simple example:
declare @cmd varchar(100)
select @cmd = 'select user_name(), getdate()'
select @cmd
The output will be: select user_name(), getdate()
Can I use something like this:
declare @cmd varchar(100)
select @cmd = 'select user_name(), getdate()'
select @cmd
so as to get an output like this:
select user_name(),
getdate()
Thanks.
View 1 Replies
View Related
Feb 25, 2005
i have a field with 2 characters(many of them ) and spaced inbetween them
which are codes for something
ie XA OX YY BY CY DY XC XD OE In any or all combination
i want to make a count of rows which contains BOTH XA
BUT DOES NOT CONTAIN any one or all of XC,XD & OE
SO I write the query
select count(colname)
from tablename
where colname LIKE ('%XA%')
AND colname NOT LIKE ('%XC%')
OR colname NOT LIKE ('%XD%')
OR colname NOT LIKE ('%OE%')
')
iS this correct
why then it does not exclude the XC,XD,OE
is there a better way ?
View 14 Replies
View Related
Oct 27, 2014
I have a huge database (20 columns and 56000 lines) and I need a query that can return each single unique character from all this database.
For example if character "a" appears at least one time in this database I need this query to show it to me.
View 2 Replies
View Related
Mar 19, 2008
Hi !
i have a query like select top 3 column1 from table 1 order by Id desc
here the column1 is of varchar(512) data type but i want to get only first 250 chars of it and if they are less than 250 then get the whole range
plz let me know how it is possible throguh query or any other way
Regards,
View 1 Replies
View Related
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
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
Jul 9, 2015
I am getting a tab character at the end of my query fields, been trying various things to fix, such as using the replace function below but still i get the tabs!
select CAST(REPLACE(NAMEALIAS,CHAR(9),'')AS CHAR(40)) + ',' as PRODNAME,
CAST(REPLACE(ISNULL(GLOBALTRADEITEMNUMBER,0),CHAR(9),'')AS CHAR(18))as EANNO,LTRIM(cast(ISNULL(GLOBALTRADEITEMNUMBER,0) as char(18))) as KONSEAN
,LTRIM(CAST(I.ITEMID AS CHAR(8))) AS PRODCODE,'00' from INVENTTABLE I
LEFT JOIN INVENTITEMGROUPITEM IG ON I.ITEMID = IG.ITEMID
[Code] ....
Results:
Gießanzeiger groß ,04260376730475 04260376730475 730475 00
Gießanzeiger klein , 04260376730352 04260376730352 730468 00
Gießanzeiger klein , 04260376730468 04260376730468 730468 00
View 9 Replies
View Related
Oct 9, 2006
Dear all,
i am using SQL server for turkish language and i have a problem with turkish character "i". Whenever "i" comes in any word of query it gives wrong result. It also not able to recognize the small and capital "i" of turkish.
View 1 Replies
View Related
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
Mar 15, 2007
I'm working in a ASP.NET 2.0 application with a SQL Server 2000 database on the back end. I have a strongly typed dataset in the application that calls a stored procedure for the select. I'm having trouble filling the dataset at runtime though.
I am trying to use a character string query because I setup different columns to be pulled from a table each time and in a different order so my T-SQL looks like this:
set @FullQuery = 'Select ' + @FieldsinOrder + ' from tblExample'exec (@FullQuery)
This works fine in query analyzer. The results return and display correctly. However, when I run the application, the dataset does not get filled. It is like the results do not output to the application.
If I change the query to be a normal select it works. For example:
select * from tblEmample
That works fine. What is it about a select query setup as a character string and then executed that ASP.NET doesn't like?
View 1 Replies
View Related
Oct 1, 2014
I'd like to return the left-most character from an 8 character string & the third from the left character too.
Like this ABC00123 returns AC
$query = "SELECT LEFT(uninum,3), RIGHT(uninum,5), clmarea, Date FROM tblunimov";
$result = mysql_query($query) or die(mysql_error());
echo "<div class='tblstyle1'>";
echo "<table class='tblstyle1'>";
echo "<tr><th>ini</th><th>item</th><th>area</th><th>date</th></tr>";
while($row = mysql_fetch_array($result)){
[Code] ....
View 5 Replies
View Related
Nov 8, 2006
People,
can I export data using BCP to ASCII file?
I'm looking for but I did not found...
Thanks
View 2 Replies
View Related
Feb 1, 2008
Hi!
I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name.
CREATE TABLE Names
(FirstName NVARCHAR (50),
LastName NVARCHAR (50));
I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character.
I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding.
I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View.
I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters.
Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also?
Thanks
int codePage = 950;
StringBuilder message = new StringBuilder();
Encoding targetEncoding = Encoding.GetEncoding(codePage);
byte[] encodedChars= targetEncoding.GetBytes(str);
.
message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':");
for (int i = 0; i < encodedChars.Length; i++)
{
message.Append("Byte " + i + ": " + encodedChars);
}
message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars));
Console.Writeline(message.ToString());
View 1 Replies
View Related
Jul 31, 2015
I want to know the individual character ascii code for "нолайн" .
when i try to get using select ascii('л') it's return code as 63 which is not correct (ascii code 63 is for "?").
how to get ascii code for this.
View 2 Replies
View Related
Oct 7, 2005
All, I have to automatically grap a dbf or ascii file from my hard drive and then insert that into an already existing database table. Does anyone know how to do this? The only thing I can find is to do it manually from enterprise manager, but I need to automate this.Thanks in advance!
View 1 Replies
View Related
Feb 16, 2001
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.
View 1 Replies
View Related
Jul 4, 2002
Hello one and all,
I’m new to T-sql and need help understanding the CHAR() and ASCII functions.
I tried to run these commands to better understand them but I get the same results each time.
Here is the query with the char command
USE Northwind
SELECT FirstName + ' ' + LastName, + CHAR(13) + Address,
+ CHAR(13) + City, + Region
FROM Employees
WHERE EmployeeID = 1
Nancy Davolio
507 - 20th Ave. E.
Apt. 2A
Seattle WA
(1 row(s) affected)
Here is the same command without the char function.
USE Northwind
SELECT FirstName + ' ' + LastName, + address, + city, + region
from employees
where employeeID = 1
address city region
------------------------------- ------------------------------------------------------------ --------------- ---------------
Nancy Davolio 507 - 20th Ave. E.
Apt. 2A Seattle WA
(1 row(s) affected)
As you can see I get the same results back except for the column names being displayed.
Please help me understand this.
Thank you,
Eric Talley
View 1 Replies
View Related
Aug 17, 2004
I have an file I FTP onto the server using a FTP task in my DTS package. The problem I'm having is the FTP task downloads using binary format, whereas I need it to download using ascii format. Binary is causing characters in the file to change, and my import is failing.
Any idea on how to set it to download as ascii?
View 2 Replies
View Related
May 30, 2006
Hi
I am importing data from mainframe using DTS the problem is that I am getting some COMP fields from the mainframe these are compressed data fields that are used to reduce storage space on the main frame. Can some one please help me understand how we can use DTS to solve this problem that is decompress it before transferring to table in SQL server.
any code in scripting language to help decompress this datatype will be really appreciated.
Thanks
View 2 Replies
View Related
Nov 6, 2006
Dear all,
I've a different problem while writing some script.
actually i need all the currency symbols and its ascii values.
i downloaded from one website, it is showing correctly in msword.
but i'm not getting the same thind in EDITPLUS.
can any one guide me in this regard please?
Vinod
View 5 Replies
View Related
Nov 6, 2006
Dear all,
I've a different problem while writing some script.
actually i need all the currency symbols and its ascii values.
i downloaded from one website, it is showing correctly in msword.
but i'm not getting the same thing in EDITPLUS.
can any one guide me in this regard please?
Vinod
View 2 Replies
View Related
Aug 29, 2006
I have data that comes from a legacy system. I can obtain the data in anASCII format. Currently I have created scripts in ACCESS to import the datainto tables.What I would like to do is create an automated import function in SQL.I am new to SQL, can anyone point me in the direction I should look to findout how I could perform this task?Using SQL 2005.ThanksMatt--Matt Campbellmattc (at) saunatec [dot] comMessage posted via http://www.sqlmonster.com
View 2 Replies
View Related
Sep 14, 2006
Hi All,
I need to generate Ascii 7 bit flat file, based on data in db, using integration services, FTP task. Currently i am generating file with ansi-latin and then using the script task converting it to the ascii 7. File looks to be generated properly. But when the target system reads this, they complain that the file has junk charecters some thing like this. when i open it after generating the file it looks fine to me in DOS also. I dont know what is the target system and what OS is used by them. what cud be the issue for these junk charecters and is it possible that a Ascii 7 file generated by windows doesnt work in other OS? If the method i am doing to generate the ascii 7 is not currect then what is the best method for this?
ÿþH^@D^@R^@|^@1^@E^@N^@I^@N^@A^@|^@O^@|^@2^@0^@0^@6^@-^@0^@9^@-^@1^@3^@
PS: earlier i had generated a flat file using data export from Excel & that worked in the target system well. is there any difference in the file encoding generated by excel and integration services?
Please help me!!!!
-vinu
View 1 Replies
View Related
May 5, 2006
I am working on a project that will be mimicking an existing interface that we have with one our our clients. That interface today sends EBCDIC packed fields. We do not want to introduce changes to the external clients interface file when we rebuild it in SQL 2005 Integration Services and I need to find out how I can take ASCII data and convert it to the host (mainframe) representation, which is what we currently provide to our external client using Integration Services.
Has anyone had to do this? If so, can I accomplish it natively with SSIS, or do I need to look to a third party vendor for a component?
Thanks,
John
john.minker@choosebroadspire.com
View 3 Replies
View Related
