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
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.
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.
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
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
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
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId
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
I have a table where the ID column is a forign key as shown below:
select * from Joel_Table
IDID_Value
1One
2Two
3Three
1Four
2Five
3Six
1Seven
2Eight
3Nine
1Ten
Now, I order the result set based on the ID column:
select ID, ID_Value from Joel_Table
group by ID, ID_Value
order by ID, ID_Value
IDID_Value
1Four
1One
1Seven
1Ten
2Eight
2Five
2Two
3Nine
3Six
3Three
I want though to order the result set as one ID per row:
ID ID_Value ID_Value ID_Value ID_Value
1 Four One SevenTen
2 Eight Five Two
3 Nine Six Three
What is the best way of accomplishing this task?
Thank you much in advance -
Hi,
I don't know if anyone has encountered this before but here goes:
I've a select clause below:
result = "Select * from person where LocalName LIKE N'" + queryLocalName + "'"
queryLocalName is an input field that allows the user to search for non-English characters in the database.
What I'm wondering is what kind of effect is the N in the where clause is having?
I can't seem to get it to work when doing it via the web. I've tested in the database itself, got it to work using the SQL Analyser but when testing on the web, it can't find because ? are appearing in the result.
Hi,
If the Database query returns no rows how do I display a message in the Report Viewer using expressions??
Thanks.
I have a query that calculates sales by sales person, but it displays horizontally across my query window. Is their a way in SQL Server to have the data display vertically down the window instead?
This is my current query
Code:
Select
count(case when salesman Like 'Geo%' then id else null end) As [George]
,count(case when salesman Like 'Li%' then id else null end) As [Lisa]
,count(case when salesman Like 'Jor%' then id else null end) As [Jorge]
,count(case when salesman Like 'Ri%' then id else null end) As [Richard]
,count(case when salesman Like 'Geo%' then id else null end)+count(case when salesman Like 'Li%' then id else null end) As [Team 1 Sales]
,count(case when salesman Like 'Jor%' then id else null end)+count(case when salesman Like 'Ri%' then id else null end) As [Team 2 Sales]
from sales.southeastregion
Which of course shows the results as such
George --- Lisa --- Jorge --- Richard --- Team 1 --- Team 2
100 50 10 90 150 100
And I want the data to be displayed like
George - 100
Lista - 50
Jorge - 10
Richard - 90
Team 1 - 150
Team 2 - 100
This is SQL Server 2008 if that matters.
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());
Hi,
Wich collation should I use for the right display of the euro character? I now get a '?'.
Thanks!
Roel
Hi there,Trying to work with SQL2005 with Hebrew characters. If i use the Unicode character data types do i need to change the database collation???. regards
View 5 Replies View RelatedHi Everybody,
Can anyone tell me apart from "sp_helpsort" system stored procedure, is there any other command to find details of Character Set,Sort Order & Unicode Collation?.
thanks,
Vasu
I have the following problem, I need to create the TotalsY and TotalsN values.
Can anybody give some direction on how to count these character values.
Column1 Y
Column 2 Y
Column 3 N
TotalsY 2
Totals N 1
Column1 =First(Fields!NOB_Pickup_L_D_T.Value)
Column 2 =First(Fields!NOB_Return_L_D_T.Value)
Column 3 =First(Fields!NOB_New_L_D_T.Value)
TotalsY 2 <I Dont Know>
Totals N 1 <I Don't Know>
Thanks,
Rick
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)?
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
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’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
How to connect to a analysis server 2005 cube,execute a mdx query and display result in a aspx page?
Please guide me
Hi this is the first time i am using sql and i have a problem with displaying the data in the way i want.
i have the following table:
EmpID Cust_Name
1 Mary
1 John
2 David
2 John
Annd i am trying to get it to display like this on the UI
EmpID Cust_Name
1 Mary, John
2 David, Andrew
is there anyway to do this?
Lets say you have to retrieve some SQL.Then you have to go through those records and delete some items, change some values, and even add columns and rows...based on logic that depends on the data you got.Thanks.
View 2 Replies View RelatedHi,
I made a table to display information from northwind database. It displays fine on IE5.0 but will only show the first line in netscape.
Do you know what it may be???
I have a column in the following select startement called displayvalue. Displayvalue is a varchar column. Primary everything entered into this column is numeric, but there are times when NR is entered. In the select query if it sees NR, I would like to have NR changed in the resultset to NULL or blank. NR doesn't come up all of the time, but I have not idea how to do this in the select statment.
Here goes..
SELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID, SDI.DISPLAYVALUE
FROM SAMPLE SAMPLE, SDIDATAITEM SDI
WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND
(SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') )
ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASC
Hello,
I am working on a project that displays images. Before you can get to the image, you have to select a category that the image may be in. After that, you select the sub categories. I am trying to display a count of the number of records that the subcategories contain. Here is an example:
The user can make a selection from the categories listed below:
Geographic Area
Time Period
Topic
Record Type
If the user selects time period, he/she is taken to a list of subcategories. I would like to display the subcategories with a count of the number of records that will be displayed if it is selected. Listed below is an example of what this would look like:
Colonial (10) -----the number in parenthesis is the number of records that will be displayed if selected------
Gilded Age (12)
Revolutionary (9)
Progressive Era (22)
Is there a way to display the number in parenthesis using ASP.Net and SQL Server 2000? Any clues will be greatly appreciated.
Thanks
Dear Pals,
I have small requirement in my project.
I need to display the results of the WHERE clause based on percentage/ranking of exact match.
I mean the result set should be displayed based on percentage match.
For example i have the below table.
create table test
(
id int identity(1,1) primary key,
ename varchar(10)
)
insert into test(ename) select 'REG'
insert into test(ename) select 'xyz'
insert into test(ename) select 'abc'
insert into test(ename) select 'Reg'
insert into test(ename) select 'Regsxysn'
insert into test(ename) select 'psReg'
I need the output something similar as below
REG
Reg
Regsxysn
psReg
I have tried out with full text indexing but i could'nt get the required output.
Any suggestions would be appreciated.
Thanks in Advance.
Dear Pals,
I have small requirement in my project.
I need to display the results of the WHERE clause based on percentage/ranking of exact match.
I mean the result set should be displayed based on percentage match.
For example i have the below table.
create table test
(
id int identity(1,1) primary key,
ename varchar(10)
)
insert into test(ename) select 'REG'
insert into test(ename) select 'xyz'
insert into test(ename) select 'abc'
insert into test(ename) select 'Reg'
insert into test(ename) select 'Regsxysn'
insert into test(ename) select 'psReg'
I need the output something similar as below
REG
Reg
Regsxysn
psReg
I have tried out with full text indexing but i could'nt get the required output.
Any suggestions would be appreciated.
Thanks in Advance.
I have a single column returned from a select statement. How can I have this returned as a vertical string? I looked into using PIVOT but my scenario seems too simple to use Pivot. I'm not requiring any aggregate functions or anything.
So, I want to turn this:
SELECT CountyNames + ',' FROM Counties
---------------------
County1 ,
County2 ,
County3 ,
..etc
TO:
County1, County2, County3...etc
Please note that the list of rows is unknown. Could be 1. Could be 50.
I written a proc to display the list of Indexes But I needed to print the database where the objects do belong to. How I should write the Dynamic script to add the database Id? I thought to use derived table kind of stuff, but unable to find a solution.
ALTER PROC [dbo].[USP_INDEXCHECK]
AS
DECLARE @sql NVARCHAR(max)
DECLARE @DB VARCHAR(max)
DECLARE databasecursor CURSOR FOR
[Code] .....
I am trying to calculate the time difference between the value in the row and the min value in the table. So say the min value in the table is 2014-05-29 14:44:17.713. (This is the start time of the test.) Now say the test ends at 2014-05-29 17:10:17.010. There are many rows recorded during that start and end time, for each row created a time stamp is created. I am trying to calculate the elapsed time and have it as a row in the results.
min(timestamp) - timestamp(value in row) = elapsed time for that test
where Channel = '273'
Here is the table DDL
CREATE DATABASE SpecTest;
USE SpecTest
GO
CREATE TABLE [dbo].[Spec1](
[Spec1ID] [int] IDENTITY(1,1) NOT NULL,
[Channel] [int] NOT NULL,
[Code] ....
Here is some dummy data to use
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
VALUES(273, '2014-05-29 14:44:17.713', 800, '-64.91');
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
VALUES(273, '2014-05-29 15:05:09.507', 800, '-59.11');
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
[Code] ....
Example desired results (I hope the formatting works)
Channel | Timestamp | Lambda | Power | Elapsed_Time
______________________________________________________________
273 | '2014-05-29 14:44:17.713', | 800, | '-64.91' | 0
273 | '2014-05-29 15:05:09.507', | 800, | '-64.91' | 00:20:51
273 | '2014-05-29 15:26:00.520', | 800, | '-64.91' | 00:41:42
273 | '2014-05-29 16:28:34.213', | 800, | '-64.91' | 01:44:16
273 | '2014-05-29 16:49:25.853', | 800, | '-64.91' | 02:05:08
273 | '2014-05-29 17:10:17.010', | 800, | '-64.91' | 02:25:59
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies View Related