Simple Excluding Of Character Query
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
ADVERTISEMENT
Sep 13, 2001
hi I have a table which contain an id field as a char(20)
The content of this field is combination of string and numbers as follow
id flag
--------- -----
38383
88585
18834
x4820
z4892
t9494
I need to update the flag field where first character in the id field is not numeric. HOw can I do that.
thanks for your hlep
Thanks
AL
View 2 Replies
View Related
Sep 5, 2007
Hi, i have a simple character problem.
In the table i have names from the entire europe, looks and works great except for the french names.
This is what i get:
Al?ay Al?abehety Sunharette
This is what it should look like:
Alςay Alςabehety Sunharette
I have tried alot of things, but its not working, also tried to change Collation to Latin1, does UTF-8 excist?
I am using MSSQL 2005
regards Patrick
View 5 Replies
View Related
Feb 20, 2007
Hi,how do I do a simple formula, which will search a field for specialcharacters and return a value.If it finds "%" - it returns 1elseIf it finds "?" it returns 2endIf this is the incorrect newsgroups, please direct me to the correct oneregards Jorgen
View 2 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
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
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
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
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
Oct 1, 2007
I am trying to make a single display page for an author's books.the books page only displays books of a type "type" (novels, non-fiction, etc)I would like to make it so that it can also show all books if "type" isn't selected. I THOUGHT the string would look like this: <asp:SqlDataSource ID="SqlDSBooks" runat="server" ConnectionString="<%$ ConnectionStrings:csK2Reader %>"
SelectCommand="SELECT * FROM [Books] ( If @Type <> "" then WHERE ([Type] = @Type)) ORDER BY [SortDate] DESC"> But it doesn't seem to want to work. I get a "server tag is not well formed" error.
View 2 Replies
View Related
Jan 7, 2008
I have a table called "member" that has two fields (amongst others) called "firstname" and "city"I want to return a list that sorts people who live in the same city, but does not return people who do not have a city in common.For example if this is my data:Karen - ParisMike - RomeTim - Dallas
Jim - ParisChris - DallasJohn - ManhattanSarah - OrlandoDavid - DallasThe query would return this:Dallas - ChrisDallas - DavidDallas - TimParis - JimParis - KarenI have this SQL so far: Select city, firstname From member Order By city, firstnamebut it includes all the cities including those that only have 1 resident.Can someone tell me how to do this?Thanks a lot,Chris
View 2 Replies
View Related
Mar 18, 2008
hi,i have three fields in the database tablep1 p2 rank10% 20% 3 21% 40% 2now i am passing the input as 15%...this 15% is related to 3rd rank.. so i need the o/p rank as 3..if the p1 and p2 datatypes are varchar..then how we can we write the query for that one tgo get the rank as 3.Ramesh
View 2 Replies
View Related
Apr 21, 2005
Hi All
Can anyone tell me what this simple SQL query would be:
Find all the words "black", "dvd" and "player" from any of the fields "Product", "Brand" or "Description".
Many thanks
accelerator
View 2 Replies
View Related
May 15, 2005
I am trying to select players from a database based on the userid not having any user's name in it with an order by ASC. I basically want all the players that haven't been taken. Here is what I came up with and it gives me empty.
strSQLA="SELECT UserID, playerName from Allplayers WHERE UserID = '' ORDER BY playername ASC"
View 4 Replies
View Related
May 28, 2006
I hope it's considered appropriate to post a SQL query question here; it's not I'll gladly post this question elsewhere. That having been said, I've run into a simple but quite tricky SQL query and was wondering if the community might be able to help. Here's what's going on:
I have a "Documents" table. Let's call it DI have a "Benefits" table. Let's call it B.There is a many-to-many relationship between D and B so I have a third table to store the relationships. Let's call that table BD.
When I create a document it creates a row in D. Let's call that D1, D2...DN. When I create a benefit it creates a row in B. Let's call that B1, B2...BN
When I associate a document to a paritcular benefit, let's say that if D1 and B2 are associated, that I created row B2-D1 in my BD table. Now here's what my challenge is. Suppose I have these entries in my tables:
D: D1, D2, D3B: B1, B2, B3
Table BD:B1-D1B2-D1B2-D2
When the user goes to add documents to benefit B1, I want to show the user only documents that are not yet added to B1. In this case, I want to show the user D2 and D3 but not D1 since that one's already added. How do I write a SQL query to do this?
Here's what I've been using, but it shows too many results (e.g. included D1, D2, D3)
SELECT D.*, BD.*FROM Documents AS d LEFT JOIN rel_BenefitsDocuments AS bd ON d.doc_id = bd.doc_idWHERE (benefit_id <> @benefit_id OR benefit_id IS NULL)
Any thoughts?
View 2 Replies
View Related
Mar 7, 2003
Hi,
I want to write a sql query for an asp page which will display only unique rows from the specified column along with the number of count for each unique row.
Example:
Table that I want to query
Last Name || First Name
Gates || Bill
Boyce || Mike
Gates || Bill
Gates || Phil
Results I want:
Last Name || First Name || Count
Gates || Bill || 2
Boyce || Mike || 1
Gates || Phil || 1
Thanks a lot,
Heta
View 2 Replies
View Related
Aug 21, 2004
Hi,
I have a very simple question:
declare @treshold int
set @treshold = 10
SELECT
dbo.fn_Calc(t.column1) as calc,
t.column2
FROM
mytable t
WHERE
dbo.fn_Calc(t.column1) > @treshold
I can't think of a way to get rid of the function call in the WHERE clause.
Is this actualy a problem ?
I mean does realy the function fn_Calc execute 2 times in this statement and isn't this a performance issue then?
thanks
View 2 Replies
View Related
Jan 17, 2002
Hi,
I am new to SQL and this question may be most easiest to many of you. Here is what I need.
I have two identical tables (exactly the same in structure) having a compound primary key with a combination of 3 columns. Can someone give me the most efficient query that fetches all the rows from table1 that are not in table2.
Thanks in advance for your help
Kevin
View 1 Replies
View Related
Oct 21, 2006
This is my query...I am attempting to only return the records for each
sales_contact with the greatest issue_id
select
sc.sales_contact_id
, idd.issue_id
, sr.code
from
sales_contact sc
, invoice i
, invoice_line_item il
, sales_region_special_section srss
, sales_region sr
, issue_date idd
where
sc.sales_contact_id = i.sales_contact
and i.invoice_id = il.invoice
and srss.sales_region = sr.sales_region_id
and il.issue_date = idd.issue_date_id
and srss.invoice_line_item = il.invoice_line_item_id
order by
sc.sales_contact_id
================
heres some of the result set I want only the records with the greatest id
for each unique sales contact...
AF85F32E-8E34-4C40-9468-00148A34E903, 41, N
AF85F32E-8E34-4C40-9468-00148A34E903, 42, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 77, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 70, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 34, BC
B44C914E-6001-40CE-8AB6-0126BD572D45, 25, NW
B44C914E-6001-40CE-8AB6-0126BD572D45, 26, NW
B44C914E-6001-40CE-8AB6-0126BD572D45, 24, NW
B44C914E-6001-40CE-8AB6-0126BD572D45, 28, HC
B44C914E-6001-40CE-8AB6-0126BD572D45 , 28, NW
this would be an ideal result set using the data above...note there are
situtations where there are n* for a greatest issue id.
AF85F32E-8E34-4C40-9468-00148A34E903, 42, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 77, N
B44C914E-6001-40CE-8AB6-0126BD572D45, 28, HC
B44C914E-6001-40CE-8AB6-0126BD572D45, 28, NW
thanks in advance for your help.
View 1 Replies
View Related
Jan 7, 2005
This should be easy, I'm just having a brainfart at the moment, can't remember how to do this:
Say I have a bunch of records in a table, with an ID field as Key. I want to return the count of times each ID shows up, so for the following example data:
Code:
-----
| ID |
-----
| 1 |
| 2 |
| 1 |
| 4 |
| 1 |
| 4 |
------
Would return something like:
Code:
----------------
| ID | Count |
----------------
| 1 | 3 |
| 2 | 1 |
| 4 | 2 |
-----------------
This should be obvious but for some reason I can't see it.
When this is done I'm going to join in another table to get a name based on the ID with the highest count. I believe it's faster to do a TOP 1 here rather than after the join, but I want to verify that while I'm at it also.
View 3 Replies
View Related
Sep 26, 2006
OLD_TABLE
CustomerPost_ClosestStore1_ClosestStore2_ClosestStore3
00501_____9339_____9193_____9445
02151_____9161_____9838_____9185
02917_____9788_____9105_____9626
06443_____9644_____9102_____9286
43434_____9258_____9496_____0
NEW_TABLE
CustomerPost_ClosestStore1_ClosestStore2_ClosestStore3
00501_____9339_____9193_____9445
02151_____9161_____9838_____9185
02917_____9788_____9105_____9626
06443_____9644_____9102_____9286
43434_____9258_____9496_____1111
(end result)
FINAL_TABLE
43434_____9258_____9496_____1111
I have two tables ... I am trying to get only the rows that are different.
View 2 Replies
View Related
Sep 19, 2005
Hi folks,
I am a complete newbie to SQL. I have a simple query to make in SQL, but I'm not being able to construct the statement properly. I've already tried googling this info, but with little luck. If someone could help me here, I'd be very thankful.
The problem:
I have a table with a list of questions. All questions have a serial number. The query has to return whether a particular question is the last one in the table or not (according to serial no.)
Logically, this is the query -
Code:
select islast = 0
if count(*) from question where serialno > $serial != 0
else
select islast = 1;
The '$serial' part will get replaced dynamically before query execution through another program.
Any idea how to code this in valid SQL? I would like to make it a simple statement and not a stored procedure. Thanks.
View 5 Replies
View Related
Sep 1, 2006
Hi,
This is probably a simple SQL statement question, so please bear with me.
Basically I have a CustomerMaster with the following fileds
1) CustomerID
2) CustName
3) Address
4) ResTelNo
I want retreive the CustName,Address and ResTelNo if the ResTelNo has occured in the table more than twice.
Could someone please help with this. I would like to use the same query in MS Access as well.
Thanks in advance for your help.
View 2 Replies
View Related
Sep 17, 2004
Hey.. hope someone can help, i desperatly need some assistance. I have a few query issues but first things first.. one is that i have a table of messages and users having an online conversation, so fields are msg id, subject, topic, message, user, reply to and time it was sent. Im trying to do a query that will tell me which user sent the most messages, ive tried using count and stuff like that but not really getting anywhere. It will basically have to count each time a user has spoken and then give me the name of the user that has sent the most messages, ive been playing around with stuff like
SELECT Count(*) AS Expr1
FROM Table1
WHERE User='andro8472' OR User='bumies';
That will count the times those users have spoken but cant get further
any help would be greatly appreciated really stuck at the mo..
thanks
View 4 Replies
View Related
Feb 29, 2008
I'm having a bad day as I just cannot get a query to work and its doing my head in and it should be a simple query.
I'll try and explain what I need.
Have a simple query with one join, this is it so far:
SELECT DISTINCT Replace(StaffName,'''''',' ') AS StaffName,OutReason,OutStartDate
,OutEndDate,OutStartTime,OutEndTime,OutID
FROM vwOutOfOffice
LEFT OUTER JOIN vwStaffList ON OutUser=StaffID
WHERE OutComplete=0
What I'm having trouble with adding to the WHERE is as follows, I need a list of Staff who are out of the office based on the various criteria:
Example
Current date=29/02/2008
Current time=14:00
Start date---End Date----Start Time--End Time--Should Appear
27/02/2008--28/02/2008--09:00------17:00-----No (because of date)
28/02/2008--03/03/2008--08:00------17:00-----Yes
28/02/2008--29/02/2008--17:00------17:00-----Yes
29/02/2008--29/02/2008--12:00------14:00-----Yes
29/02/2008--29/02/2008--14:30------17:00-----No (because of time)
02/03/2008--02/03/2008--08:00------10:00-----No (because of date)
There may be others I've missed but as long as the dates combined with the times match correctly I need the records to appear.
Please help before I end up putting my head through the monitor.
View 2 Replies
View Related
