I have to write one query to exclude data from partial string in the predicate.
Select * from table where col NOT in ('%abc%,%xyz%).
So the data where col value doesn't contain the ABC and xyz string value only should be selected. I am also getting xyz data in the result.
Could someone please help me? I am trying to pull in a partial string (the last six characters of the field, to be exact).
This is an example of my code:
select *
into #temp_2
from #temp_1 a, Server2.DBa.dbo.table2 r
where r.field1r = a.field1a and
r.field3r = a.field3a (field3a is where I need just the last 6 characters)
To be more specific:
r.field3r looks like 000884
a.field3a looks like 17445000884
So- I just want to pull in the 000884 off of a.field3a
Hello All,
I am looking for an expression for a group in a matric. I am trying to figure out how to group by the a certain amount of letters in a string. For example if I have the followong fields I am grouping...
Bob001
Bob
Robert005
Doug053
Doug100
Douglas
Barney001
Frank
I want to group it up as...
Bob
Doug
Barney
Frank
And then be able to summarize the results in the matrix.
Thanks in advance for any help
-Clint
Hi, I test the following sql statement, finding that an error ocurs:
Msg 7630, Level 15, State 2, Line 3
Syntax error near '"' in the full-text search condition '"dsg SDRGDG " OR "sdfsdfsdfsdafdsafdsfds'.
DECLARE @searchTerm NVARCHAR(40)
SET @searchTerm = '"dsg SDRGDG " OR "sdfsdfsdfsdafdsafdsfdsafdsafdsafsafdfdsafdf"';
SELECT [JobTitle], [JobDes], [OpenDate], j.[URLRef], c.[CompanyName], c.[URLRef], c.[URLSource]
FROM JobWanted AS j INNER JOIN
Company AS c ON c.CompanyID = j.CompanyID
WHERE CONTAINS((JobTitle, JobDes), @searchTerm)
It seems too lengthy string will cause an error for full-text engine. I find the sdfsdfsdfsdafdsafdsfdsafdsafdsafsafdfdsafdf is truncated as shown in error message.
How to avoid this issue? Could I configre this limination?
Thanks in advance.
Ricky.
Is it possible using Sql2000 to compare the first four characters of a parameter?
WHERE (tblLinkInfo.CG_ID = @BCG_ID) AND ( 'first 4 characters' of Location_Zip.Zip = 'first 4 characters' of @ZipCodeIn)
Sorry, I realize that this is probably a pretty simple question for you, but I am in a quick time bind and would really appreciate it if someone could help me with this update that I'm trying to run.
Let's say you have a table (call it testtable) that contains the column "text".
Now in column text you want to replace all instantiations of the phrase "in the US" with "to the United States", how would you structure your update query to perform this change (Please note that this phrase could be embedded in the middle of a larger sentence like 'Send a package in the US'. You'd want to change this instance to 'Send a package to the United States')
Thanks in advance, I really appreciate it guys.
how do i update a table which has like two strings in 1 column like
blog, joe ?
i want to strip the joe into a new field and the blog into another field
update Agency
set firstname= substring(firstname,charindex(' ',firstname)+1 ,len(firstname))
i managed to strip the first name which is the string at the back but not the last name which is the string at the front
Hi! I need to replace a string in a SQL table with a new path.
Essentially, I want to find and replace the share path while retaining the rest of the path past that point.
I tried using Update (on a test table of course) but it sompley find and replaces with no regards to the rest of the path.
Any help is greatly appreciated!
JJ
Daily, I have to map thoursands of items into its correct category. What I have to do is Filter item by the ClSID and SCLISD, then look into the Label for where the item should be. For example from the attachment,
CLSID = 1 and SCLSID = 23 - if in the label has the keyword "notebook", its NEW_CAT should be 14 and its NEW_CATNAME is LAPTOPS;
CLSID = 1 and SCLSID = 23 - if in the Label contains Tablet PC, its NEW_CAT name should be 2474 and its NEW_CATNAME is PC LAPTOPS.
CLSID = 3 and SCLSID = 54 - if in the Label contains Watch, its NEW_CAT name should be A6052 and its New_CATNAME is SMART WATCH.
Basically, the work flow is looking into the CLSID, then SCLSID,then, Label and then assign a correct NEW_CAT and NEW_CATNAME of the item.
I have written a code but I can't get it look into specific CLSID and SCLSID.
For example, i have when upper(Label) like upper ('%Tablet%PC%') then '2474 PC Laptops'
I couldn't attach either the file or image.
Select
left(
[Description],(charindex(';',[Description],1)-1))
from xxxx
Example of Description contains
Ankle Supports; Color=Black; Size=S
So I want the left side up to and NOT including the semi colon.
hi ,
what is the definition and difference between predicate and residual predicate.
give me some examples..Basically the columns used in where clause are called as predicates. Am i right.
I'm new to MS SQL server. Is there a select statement that will include fields that are not null?
View 2 Replies View RelatedI am selecting the following fields
AVG_Back
AVG_Yield
I want to select both fields, like this
Select AVG(AVG_Back), AVG(AVG_Yield)
FROM tblUser
WHERE Date Between '3/1/2008' AND '3/31/2008'
I want to limit the AVG_Back field to exclude all values of 0. So only average AVG_Back if the value > 0. What is the best way to accomplish this? I can't just put it in the where clause or the AVG_Yield will be excluded too.
I have a column that is VARCHAR(30) this column is supposed to contain values that "look" like a date in the form mm/dd/yyyy - however because it is a free-text character field often times data is entered other then a date -- "text" -
How do I return only the data that is in the format of mm/dd/yyyy
I moved a database from one server to another, in this database are references to a UNC path on the server. now I need to find a way to modify the server name that was in each row to reflect the new server name and path, ex:
\server1path1data to now say
\server2path2data
how do I selectivly modify just some of the data? or do I have to basically re-write the entire column for each row?
Hello,
I have a table in witch i would like to replace all the data form one columm following a criteria.
The criteria is:
Where 1010222222 will be 0020222222
So i want to make a script that enables me to substitute all the data in that columm that begin by "101" for "002"
Any help?
Thanks
I have a table called MasterSkillList which is a list of skills and attributes, eg: [Appraise, INT], [Bluff, CHA] etc
I have a table called Classes, which is a list of all classes available (and some details which are irrelevant), eg: [Fighter], [Assassin] etc.
I also have a table called ClassSkills which holds a list of classes and their applicable skills, eg: [Assassin, Bluff], [Assassin, Open Lock], [Fighter, Appraise], [Fighter, Bluff] etc.
What I have is a gridview which shows all my classes from the class table. i want to be able to select a class on that gridview and create a checkbox list of all available skills that are NOT allready associated with that class. Eg: assassin has bluff and open lock, so those two skills shouldn't appear on my checkbox list. So i want to show all the skill from the master skills list, excluding all the skills the selected class allready has.
Alternatively, It would be better if there was a way to display all the skills in existance on my checkboxlist and the ones that class allready has to be checked. Any suggestions?
Here's the query I have:
SELECT MasterSkillsList.SkillFROM ClassSkills INNER JOIN MasterSkillsList ON ClassSkills.Skill = MasterSkillsList.SkillWHERE (MasterSkillsList.Skill <> ClassSkill.Skill)
Edit:
I just added the following sql query, but when i run it i get no results even though it should show everything except 2 skills. Have I written it wrong?
SELECT SkillFROM MasterSkillsListWHERE (NOT EXISTS (SELECT Skill FROM ClassSkills WHERE (ClassName = @ClassName)))
I have a cube that is showing measures that don't exist. Let me give an example. This example will include 3 dimensions, product, location, and time. The fact table measure will be sales.
Here are the distinct values if you were to write a sql query against the dimensionl model that feeds the cube.
Product Location Time Sales
A X 1/04 200
B Y 1/04 100
A X 2/04 300
In the cube, if you were to look at product by location for just 2/04, you would see:
Product Location Sales
All Loc 300
A X 300
Y
All Loc
B X
Y
How do you get rid of the zero's or combinations that don't exist?
Thanks,
Doug
Hi All,
We've got a basic query that pulls a list of some parts out of our inventory database:
SELECT p21_view_inv_mast.item_id, p21_view_inv_mast.item_desc, p21_view_inv_loc.gl_account_no
FROM dbo.p21_view_inv_loc p21_view_inv_loc, dbo.p21_view_inv_mast p21_view_inv_mast
WHERE p21_view_inv_loc.inv_mast_uid = p21_view_inv_mast.inv_mast_uid
This returns a lot of info over three columns: "item_id" "item_desc" and "gl_account_no"
What I want to do is remove any entries where the "gl_account_no" shows as 011500000
I am by no means a TSQL person, I mostly run the canned queries that our vendor gives us so any help with this would be greatly appreciated.
After converting from SSIS 2008 to SSIS 2012, I am facing major performance slowdown while loading fact data.When we used 2008 - one file used to take around 2 hours average and now after converting to 2012 - it took 17 hours to load one file. This is the current scenario: We load data into Staging and Select everything from Staging (28 million rows) and use a lookups for each dimension. I believe it is taking very long time due to one Dimension table which has (89 million rows).
With the lookup, we currently are using partial cache because full cache caused system out of memory.Lookup Transformation Editor - on this lookup - how to increase the size on partial Cache size 64-bit? I am being stuck at 4096 MB and can not increase it. In 2008, I had 200,000 MB partial cache size.
Based on a table like below I have created a report so that I can compare number of items in the main warehouse (LOCATION1) and the outlets (LOCATION2 and LOCATION3).
___________________________________
| ID | PRODUCT_INDEX | LOCATION | VALUE |
___________________________________
| 1 | INDEX1 | LOCATION1 | 1 |
___________________________________
| 2 | INDEX1 | LOCATION2 | 1 |
___________________________________
| 3 | INDEX1 | LOCATION3 | 0 |
___________________________________
| 4 | INDEX2 | LOCATION1 | 0 |
___________________________________
| 5 | INDEX2 | LOCATION2 | 0 |
___________________________________
| 6 | INDEX2 | LOCATION3 | 1 |
___________________________________
| 7 | INDEX3 | LOCATION1 | 1 |
___________________________________
| 8 | INDEX3 | LOCATION2 | 0 |
___________________________________
| 9 | INDEX3 | LOCATION3 | 1 |
___________________________________
The way I present data in my Report is as such. I want to show items that are available in the warehouse that should be moved to the outlets.
select
a.PRODUCT_INDEX
, a.LOCATION1(VALUE)
, b.LOCATION2(VALUE)
, c.LOCATION3(VALUE)
from
[Code] .....
__________________________________________________________________
| PRODUCT_INDEX | LOCATION1 (VALUE) | LOCATION2 (VALUE) | LOCATION3 (VALUE)|
__________________________________________________________________
| INDEX1 | 0 | 1 | 0 |
__________________________________________________________________
| INDEX2 | 1 | 0 | 1 |
__________________________________________________________________
| INDEX3 | 1 | 0 | 1 |
__________________________________________________________________
I have added some parameters in my report to filter out products that are not available in warehouse (LOCATION1) and this works great.
select * from VIEW where 'LOCATION1(VALUE)' > 0 and ('LOCATION2(VALUE)' = 0 or 'LOCATION3(VALUE)' = 0)
__________________________________________________________________
| PRODUCT_INDEX | LOCATION1 (VALUE) | LOCATION2 (VALUE) | LOCATION3 (VALUE)|
__________________________________________________________________
| INDEX1 | 1 | 1 | 0 |
__________________________________________________________________
| INDEX3 | 1 | 0 | 1 |
__________________________________________________________________
Now the issue starts when I add a parameter to my report for user to choose which outlets (LOCATIONs) he wants in the equation. I know how to make a column disappear based on parameter value but how to take it out of equation? At the moment when user selects only LOCATION2 and not LOCATION3 then data is not filtered correctly:
__________________________________________________
| JOIN_ON_VALUES | LOCATION1 (VALUE) | LOCATION2 (VALUE) |
__________________________________________________
| INDEX1 | 1 | 1 |
__________________________________________________
| INDEX3 | 1 | 0 |
__________________________________________________
Ideally I would like a user to select random outlets (warehouse would be static on the report) and compare one or multiple and only show records that are 0 in the outlets.
I am trying to insert a row into a table of Microsoft SQL Server 2000.
There are various columns.
[SNO] [numeric](3, 0) NOT NULL ,
[DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-
StaleConnecti A CONM7007I: Mapping the following
SQLException, with ErrorCode 0 and SQLState 08S01, to a
StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC]Connection reset
at
com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?
Can anyone please tell me what's wrong?
Hello, I am tring to add a string my database. Info is added, but it is the name of the string, not the data contained within. What am I doing wrong? The text "Company" and "currentUserID" is showing up in my database, but I need the info contained within the string. All help is appreciated!
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClientPartial Class _DefaultInherits System.Web.UI.Page
Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'First Command DataDim Company As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Company"), TextBox)).Text)
Dim insertSQL1 As StringDim currentUserID As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox)).Text)
insertSQL1 = "INSERT INTO Company (CompanyName, UserID) VALUES ('Company', 'currentUserID')"Dim cmd1 As New SqlCommand(insertSQL1, con)
'2nd Command Data
Dim selectSQL As String
selectSQL = "SELECT companyKey FROM Company WHERE UserID = 'currentUserID'"Dim cmd2 As New SqlCommand(selectSQL, con)
Dim reader As SqlDataReader
'3rd Command Data
Dim insertSQL2 As String
insertSQL2 = "INSERT INTO Company_Membership (CompanyKey, UserID) VALUES ('CompanyKey', 'currentUserID')"Dim cmd3 As New SqlCommand(insertSQL2, con)
'First CommandDim added As Integer = 0
Try
con.Open()
added = cmd1.ExecuteNonQuery()
lblResults.Text = added.ToString() & " records inserted."Catch err As Exception
lblResults.Text = "Error inserting record."
lblResults.Text &= err.Message
Finally
con.Close()
End Try
'2nd Command
Try
con.Open()
reader = cmd2.ExecuteReader()Do While reader.Read()
Dim CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
lbl1Results.Text = "Error selecting record."
lbl1Results.Text &= err.Message
Finally
con.Close()
End Try
'3rd Command
Try
con.Open()
added = cmd3.ExecuteNonQuery()
lbl2Results.Text = added.ToString() & " records inserted."Catch err As Exception
lbl2Results.Text = "Error inserting record."
lbl2Results.Text &= err.Message
Finally
con.Close()End Try
End Sub
End Class
I run statement down,result is "go". why goes,gone,going is not in the result?
SELECT ID, firstname, lastnameFROM [contain-1]WHERE CONTAINS(firstname, ' FORMSOF (INFLECTIONAL, go) ')
information table [contain-1]
ID firstname
1 go
2 goes
3 gone
4 going
what result this query?
SELECT productname
FROM product
WHERE CONTAINS(productname,'spread NEAR boysenberry' )
explain word "NEAR".
Thanks,mohsen
I run query down.result is null.
SELECT ID, firstname, lastname
FROM [contain-1]
WHERE CONTAINS(firstname, 'pooya NEAR yaser')
table [contains-1] information:
ID firstname
1 pooya
2 mehdi
3 peter
4 yaser
why result is null?
thanks,mohsen
I run query down.result is null.
SELECT ID, firstname, lastname
FROM [contain-1]
WHERE CONTAINS(firstname, 'pooya NEAR yaser')
table [contains-1] information:
ID firstname
1 pooya
2 mehdi
3 peter
4 yaser
5 pooyaandyaser
why result is null?
thanks,mohsen
I run query down.result is null.
Why is result null?
SELECT ID, firstnameFROM [contain-1]WHERE CONTAINS(firstname, 'yaser NEAR pooya')
table information:
ID firstname
1 ali
2 peter
3 yaser
4 yaserapooya
5 mehdi
Thanks,mohsen
Hi,i want to search record in a table where a varchar column is equal toa word or this word with the letter 's' or this word with the letter'e'.Today, i've this query :select * From Table Where Column = 'Word' or Column like 'Word[es]'I want to remove the 'or' of this query to have only one condition.Does someone know a solution to my problem.Excuse my poor english language.I hope you understand my problem and thks for solution.
View 2 Replies View RelatedHi everybody,
I have a problem using the CONTAINS predicate. I use the contains
predicate in a web page that students utilize to look for dissertations
stored in a database (MS 2000) of the library. The search is one
big textbox where students can perform any type of reaserch. For
instance, they can insert the author, the title or few keywords of a
dissertation. After they have clicked on the button Submit, the words
that they have inserted are filtered and combined (with AND) in
one string:
Example: miami, education --> 'Miami AND Education'
An example of the query that I need to perfrom is the Query Number 1:
select docnumber,Title
FROM [Log].[dbo].[DocTable1]
where contains(SearchColumn,'miami AND education') order by docnumber
SearchColumn is a field (varchar) that is the concatenation of various
fields such as Location, Title, Author, etc... that are contained in
the same table (See below for the description of the Table DocTable1).
I'm currently testing the search and I have figured out that the query described above does not work.
For example, the query should produce the same resultsof the following one, but it does not:
select docnumber
FROM [Log].[dbo].[DocTable1]
where contains(Location,'miami') AND
contains(Keywords,'education') order by docnumber
I need to implement the first query, but i can not understand why it
does not produce the same results of the second one. Any Ideas?
Thanks,
Christian
--------------------------------------------------------------
CREATE TABLE [DocTable1] (
[DocNumber] [int] NOT NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Program] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Paper] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ANNO] [int] NULL ,
[Title] [varchar] (350) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TitleSort] [varchar] (350) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Keywords] [varchar] (350) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[URL] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SearchColumn] [varchar] (900) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_DocTable1] PRIMARY KEY CLUSTERED
(
[DocNumber]
) ON [PRIMARY] ,
CONSTRAINT [Title_Author_DocTable1] UNIQUE NONCLUSTERED
(
[Title],
[LastName],
[FirstName]
) ON [PRIMARY] ,
CONSTRAINT [FT-Constraint] CHECK NOT FOR REPLICATION ([FT] = 'Y' or [FT] = 'N')
) ON [PRIMARY]
GO
I am having problems with the following SQL statement. It works how I want it to work if I use = @SearchQuery but when I try to switch to LIKE I run into problems. I tried it the way it is shown below and I also tried LIKE '%'@SearchQuery'%' with no luck. Does this have something to do with the pattern to match being a parameter? How would I go about doing this correctly.
"SELECT server_info.*, network_adapter.* FROM server_info LEFT JOIN network_adapter " + "ON server_info.server_id = network_adapter.server_id WHERE " + DropDownList1.SelectedValue.ToString() + " LIKE '%@SearchQuery%'"
Thanks
Anybody know how to do this?
"Select something from some table where id in (@IDlist)"
where @IDlist is a comma-separated list of values (or something top that effect)
HiI am having a debate with one of the Postgres developers (Tom Lane)according to him ms-sql does not implement predicatelocking(SERIALIZABLE).Where predicate locking is defined as (from the postgres help):"12.2.2.1. Serializable Isolation versus True Serializability"It states: "To guarantee true mathematical serializability, it isnecessary for a database system to enforce predicate locking, whichmeans that a transaction cannot insert or modify a row that would havematched the WHERE condition of a query in another concurrenttransaction"Now I put it to him that that is exactly what SERIALIZABLE does isMS-SQL.But his response was "Only for WHERE conditions that can be expressedas a simple range constraint."Now is he correct i.e SERIALIZABLE works "Only for WHERE conditionsthat can be expressed as a simple range constraint." ?Regards,Daniel RothMCSD.NET
View 8 Replies View Related