I want to write a statement something like this
SELECT Add_Date, File_No FROM dbo.File_Storage WHERE (File_No = 11/11/1234/)
But i want the search to ignore the first 2 digits so that it will return e.g
10/11/1234, 09/11/1234 so that it's only matching the last part
Any Help Would be greatly appreciated Thanks
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
Our clients want to be able to do full text search with a single letter. (Is the name Newton, Nathan, Nick?, Is the ID N1, N2...). Doing a single character full text search on a table work 25 out of 26 times. The letter that doesn't work is 'n'. the WHERE clause CONTAINS(full_text_field, ' "n*" ') returns all rows, even rows that have no 'n' in them anywhere. Adding a second letter after the "n" works as expected.
Here is an example
create table TestFullTextSearch (
Id int not null,
AllText nvarchar(400)
)
create unique index test_tfts on TestFullTextSearch(Id);
create fulltext catalog ftcat_tfts;
[Code] ....
I'm not quite sure that this is possible but, I figured that I would check with you experts out there before trying a new approach. I've done quite a bit of research and have not seen anyone quite figure this out yet.
We have a SQL Server 2005 application that stores and indexes documents to the database as an image data type. I'm able to do full-text queries against the documents without any trouble. I begin to run into problems when trying to pattern match social security numbers and drivers licenses stored in a full-text index. I have a user defined function that I call which runs my regular expression that checks for hits of a ssn or license number in the index. I have no problem getting hits when the data sits in a column.
I do need to mention that I have no trouble when searching for a ssn with a fixed value and where I know the ssn (ex: 123-45-6789). I am actually trying to find the existence of the pattern of ###-##-#### (ex: ^d{3}-d{2}-d{4}$) anywhere in the index.
Any help would be very much appreciated.
i implement and enabled MS Search and tables to be full text, created search catalog and build the catalog. but a SELECT statement on a field using CONTAINS returns no row, anything i do wrong ?
I have seen an article that for a table to be full text search capable a field TIMESTAMP must be in the table, is this true ?
Please help.
Thanks in advance.
regards,
bugfix
I apologise if this is a stupid question, but I would appreciated anyhelp on this subject.I want to create a view (VIEW_1 in example below) where I take numericvalues from a field in one table (DEPTH_FROM in TABLE_1) and find theclosest matching values from another field in another table (DEPTH_ATin TABLE_2) - the higher matching value and the lower matching value.TABLE_1BH_ID DEPTH_FROMB1 10.6B1 14.2B1 16.1B1 17.0TABLE_2BH_ID DEPTH_ATB1 9B1 10B1 11B1 12B1 13B1 14B1 15B1 16B1 17B1 18VIEW_1BH_ID DEPTH_FROM DEPTH_LOWER DEPTH_HIGHERB1 10.6 1011B1 14.2 1415B1 16.1 1617B1 17.0 1717Any assistance would be appreciated.Regards,Hennie
View 2 Replies View RelatedHi,
I have a table of parts which has a Tag column. The table is such that two parts can share a tag (but no more than two parts), essentially pairing them. I would like to use SSIS to match the parts based on this column value, so that I can add these into a new table.
For example, I could have:
(part1, tag-123, "a random part", serial123) and
(part2, tag-123, "another random part", serialxyz)
in the table, and would then concatenate the pair to enter into a new table, such that I have:
(part1, tag-123, "a random part", serial123, part2, tag-123, "another random part", serialxyz)
I'm new to SSIS and am unsure how I could achieve this, so if anyone could show me how I would appreciate it.
Thanks,
Simon
I googled on the latter error number and got nothing back
Error: 0xC0048006 at Set Destination File, ActiveX Script Task: Retrieving the file name for a component failed with error code 0x016F9EA4.
The former only returns one result which does not explain my problem
In sql server 2000 - our QA pointed out that his testing for empty strings returned 200 + rows but that when he clicked in the field there were obviously a space there. This issue came up because of the script I created to replace and earlier one that queried on empty strings instead of datalength and the earlier script always reported that it had updated x number of rows regardless of how many times it was run on the same database.
QA query based on the earlier script:
Select * from StringTable
WHERE (LongString = '' OR LongString IS NULL)
My script:
The fields are nvarchars in the newer database but older version of the database had varchars. I had created a script to replace empty strings as follows:
-- if LongString column is varchar - run varchar update else nvarchar update
If exists (Select * from sysobjects o
inner join syscolumns c on c.id = o.id
where c.name = 'LongString' and o.name = 'StringTable' and c.xtype = 167) begin
-- update varchar LongString
UPDATE StringTable
SET LongString = char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(LongString ) < 1) OR LongString IS NULL)
END
Else Begin
-- update nvarchar LongString
UPDATE StringTable
SET LongString = char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(LongString ) < 2) OR LongString IS NULL)
END
If exists (Select * from sysobjects o
inner join syscolumns c on c.id = o.id
where c.name = 'ShortString' and o.name = 'StringTable' and c.xtype = 167) begin
UPDATE StringTable
SET ShortString= char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(ShortString) < 1) OR ShortString IS NULL)
END
Else Begin
-- update nvarchar ShortString
UPDATE StringTable
SET ShortString= char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(ShortString) < 2) OR ShortString IS NULL)
END
My method for checking for datalength appears to work correctly why doesn't the QA script? I thought it might have to do with the nvarchar used in the table but I changed the column to a varchar and still has the same issue.
Thanks
Hello, all.
I am new to SQL querying and I came across an issue while experimenting.
Say, I have two tables like the following, a Users table at the top, and a Roles table at the bottom (drawing the issue as I'm not fluent in English to explain):
(Users table)
UserId | RoleId | RoleOldId
-------+--------+----------
1......|x.......|11........
2......|y.......|22........
3......|z.......|22........
(Roles table)
RoleId | RoleOldId | RoleName
-------+-----------+-------------
10.....|11.........|Anonymous....
20.....|22.........|Superuser....
30.....|33.........|Administrator
The Roles table was changed to include a new column Roles.RoleId and the old column is named Roles.RoleOldId. Same is done for Users table.
Assume these are not foreign and/or primary keys. How can I fill Users.RoleId with new Roles.RoleIds, matching RoleOldIds at each tables? The resulting (x, y, z) set of Users table will be (10, 20, 20).
Thank you for your time.
I have tables in my database, tblNames1, tblNames2, tblNames3 for ex, and for addresses tblAddresses, can I match the address IDs in the names tables to the address table based on the values of a field? In this case CompanyName. Can they be matched automatically? My issue is that I have a huge CSV file with all the address information that needs to be assigned a foreign key based on the company name field which is also present in all the names tables, all the names are normalized.
View 1 Replies View RelatedI need to write a statement that returns the name, city, and state of each vendor that’s located in a unique city and state. In other words, I can not include vendors that have a city and state in common with another vendor.
Any help?
Thanks.
I want to check for multiple patterns in a particular column.
For one pattern I can write e.g.
SELECT * FROM <TablName> WHERE ColumnName LIKE '%abcd%'.
My requirment is to select all rows for which column value matches with many patterns.I will fetch the patterns in a subquery
e.g. (SELECT '%'+name+'%' FROM <TableName>)
Any thoughts?
I have the following script below which checks my employee table for duplicate employees based on surname, forename, dateofbirth and NINumber and inserts the duplicates into a temp table i.e. @temptable. I now want to change how NINumber is matched. For example i want to now say:
match on the 1st 8 characters of NI Number where the NI Number does not start with TN.
For NI Numbers that start with TN match on all 9 chars.
Can anyone help with the syntax?
code is as follows:
DECLARE @temptable table(f_id int primary key identity(1,1), NhssNumber int, Surname varchar(18), Forename varchar(11), DateOfBirth datetime, NINumber varchar(9), MaxNhssNumber int)
INSERT INTO @temptable
SELECT
a.NhssNumber,
CAST(a.Surname as varchar(18)) as Surname,
CAST(a.Forename as varchar(11)) as Forename,
CONVERT(char(8),a.DateOfBirth,112) as DateOfBirth,
CAST(a.NINumber as varchar(9)) as NINumber, NULL
FROM dbo.t_Employee a
JOIN
(SELECT Surname, Forename,DateOfBirth,NINumber
FROM dbo.t_Employee
GROUP BY Surname, Forename,DateOfBirth,NiNumber
HAVING COUNT(*) > 1) b
ON a.Surname = b.Surname
AND a.Forename = b.Forename
AND a.DateOfBirth = b.DateOfBirth
AND a.NiNumber = b.NiNumber
ORDER BY a.Surname, a.Forename,a.DateOfBirth,a.NiNumber
select * from @temptable
We have 2 tables (table a and b)
select a.* from table a inner join table b
on a.col1<> b.col2
I would like to have column names where the values are not matching.
Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E
can any one help me in this?
please give me a sample query.
Thanks and Regards,
Kiran Suthar
Hi all,I'm very new to ASP.NET stuffs, I'm trying to write a Search function for my website... I have two text boxes, one if called "SongTitle" and the other is "Artist"... Now I need to populate the GridView to display the result, based on the input of the textbox... So if only the "SongTitle" have input, it will search for the Song Titles on the database... if the Artist is searched, then it will return the artist... If both text boxes have value in them, then it need to check for both fields in the database and return the correct item... For the "Artist", I have 2 columns in the Database (originalArtist and performer), so for the Artist select statement, it need to check both columns on the table, if any of them match then it will return the item. Any help would be greatly appreciated,Thank you all,Kenny.
View 15 Replies View RelatedI have 3 table
table_A
table_B
table_C
TABLE_A
SNO NAME ID
1 RAJU 070491
2 VAMSHI 089767
3 ARUNA 068908
TABLE_B
SNO NAME ID
2 RAJU 070491
4 JKLKJ 098766
I WANT COMPARE TWO TABLES(TABLE_A & TABLE_B) SELECT TABLE_A MATCHING VALUES COPY IN TABLE_C
EX-
SNO NAME ID
1 RAJU 070491
Hi all sql gurus,
I have a table that has product name and submissionID and some other columns. I am really concerned about these two columns. My task is to get all the submissionIDs for a particular product name and display SubmissionIDs seperated by commas against each product name .
The tables below might give a better idea
current scenario:
Product Name SubmissionID columnC Column D
AAA 123
AAA 456
BBB 111
ccc 121
AAA 789
Expected result:
Product Name SubmissionID columnC Column D
AAA 123,456,789
BBB 111
CCC 121
Any suggestions/pointers would be highly appreciated.
I have written sql query
select INVOICE.InvoiceTypeCode, INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,
INVOICETYPEMASTER.InvoiceTypeName, INVOICEITEMS.ItemQuantity as SumQuantity,
INVOICE.BasicValue ,INVOICE.BasicValue * INVOICE.ExchangeRate +
[Code].....
I am getting different amount 984000.0000 and quantity 9.
Is It possible to have a stored procedure that returns 2 values?
and call it from a C# webforms application.
Thanks.
I'm trying to understand why I can enter a query such as:
select 5,"random"
from customers;
and get two columns with 5 and "random" in every respective column field.Why don't I receive a syntax error ?
Hello all,
I am writing Stored Procedure to perform various kind of search on Database Tables.
I having requirement like Exact Phrase,Any of the word,All Words and Refine search.
I am using Full text Queries for this.
I am stuck with "All Words" search query. Can anyone please guide me how to write query to perform this kind of search. Below is the little elaboration about All words search.
Query should return all the results which contain all the words from search phrase irrespective of order.
e.g. if i search am searhing "Green House Effect" phrase then query should return all the records which having these three words in any order. like Green fds dfasd House fdsd fdsdf Effect or House fds fds Green Fd fds Effect or Effect fsda fdsa Green fd fdsa HOuse.
all the records selected by query must have all words exist in any order.
Suggest me the logic or query or any SQL Server keyword which i can use for this.
Please do the needful ASAP. Thanks.
Hello ALl, can anyone please tell me how to search for NULLS.
like i know one method....
select * from table
where col = 'NULL'
I have tables and a function as representated by the code below. The names for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.
When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.
SELECT A.CID,
A.ANI,
A.PID,
B.RD
FROM T1 AS A CROSS APPLY GIVERD(A.PID) B
CREATE TABLE [DBO].[RDTEST](
[PID] [INT] NULL,
[RD] [INT] NULL
)
[Code] ....
I am hoping someone could help me understand why this is happening and perhaps a solution.
I am using ASP.NET 2.0 with a SQL 2005 database.
In code behind, I am performing a query using a parameter as below:
sql = "SELECT field_name FROM myTable WHERE (field_name = @P1)"
objCommand.Parameters.Add(New SqlParameter("@P1", TextBox1.Text))
The parameter is obtained from TextBox1 which has valid input. However, the value is not in the table. The query should not return ANY results. However, I am getting one single row back with null values for each field requested in the query.
The SQL user account for this query has select, insert, and update permissions on the table. The query is simple, no joins, and the table has no null values in any fields. If I perform the exact same query using an account with select only permission on the table, I get what I was expecting, no records. Then if I go back to the previous user account with more permissioins, and I change the query to pass the paramter this way:
sql = String.Format("SELECT field_name FROM myTable WHERE (field_name = {0})", TextBox1.Text)
I also get NO records retuned using the same criteria.
What is going on here? I would prefer to use the parameterized query method with the account having elevated permissions. Is there some command object setting that can prevent the null row from returning?
Thanks!
I have following select-statement:
select [date], [close], AVG([close]) over (order by [date] rows between 2 preceding and current row ) as [ma]
from dax2
My Problem is that the first 2 rows in column [ma] are not correct. They Show a value since it is not a 3 days average. In the first row in column [ma]is the same value as in [Close]. In the second row in column [ma] is the average value of the first and second value of column [Close].
How can i achieve that this "erroneous" values are not inserted or rather are shown as null.
Anyone seen wrong values returned from a CLR update trigger when using the columncount property?
I have a 6-column table that I was experimenting on, and the isupdatedcolumn property was not returning true on the one column I was updating (I verfied the trigger was firing). So, I returned the columncount property in a pipe.send, and was surprised to see the value of 11. When I looped through each column's isupdatedcolumn property, the only column that returned true was column 9.
Then I remembered this table used to have more columns, so I conducted the following experiment:
Added a new column to the table (bringing the total number of columns to 7).
Executed an update the caused the trigger to fire; columncount sent to the pipe returned 12.
Deleted the column.
Executed another update that caused the trigger to fire; columncount returned 12.
Added another column to the table.
Executed an update the caused the trigger to fire; columncount sent to the pipe returned 13.
Deleted the column.
Executed another update that caused the trigger to fire; columncount returned 13.
See the pattern? I'm fairly perplexed. Anyone seen this or something similar? This table is in a database that was migrated from SQL 2000 to 2005 and is 9.0 compatibility mode.
Thanks,
I'm fairly new to SQL, so this might be simple question:
I am adding records to an SQL7 database by using the INSERT statement. The table has an IDENTITY field which is auto-incremented, so a value is not needed for the field in the query.
Is there any parameters for INSERT that returns to me the value of the IDENTITY field for the record I just created?...
Any help or suggestions would be appreciated.
I have this OR in a simple query:
Code:
DECLARE @searchString nvarchar(100)
SET @searchString = 'sample'
SELECT TOP(1) * FROM user
WHERE (user.identity LIKE @searchString OR
CHARINDEX(@searchString, user.firstname + ' ' + user.lastname) > 0)
PS: Handwritten...
If I pass in a searchString that matches the Identity and a different users lastname, this query will return the user with the lastname-match (which is wrong in my eyes, it should have matched the Identity first, then returned that row [Identity is a Primary key, indexed non-clustered]).
I've tried various things:
Removing LastName: (CHARINDEX(@searchString, user.firstname + ' ') > 0), then the returned row is from a matching identity, due to the lastname of a user was a match, but lastname was removed...so :P
Flipping the conditions around
Adding/removing paranthesis... without any luck.
Is there some option somewhere, to force the OR statement to return on the first true condition. I've always thought OR-statements (in a computer that is) breaked and returned true on the first true condition it found (from left to right, not bother to check the rest of the conditions...)?Or do I have to rewrite the query, with an IF, checking if the @searchString is a valid identity-format, if it is, query on the identity, if not query on the username...?
I am using the following conditional select statement but it returns no results.
Declare @DepartmentName as varchar
Set @DepartmentName = null
Declare @status as bigint
Set @status = 4
IF (@DepartmentName = null) BEGIN
SELECT CallNumber AS [Call Number], Problem, Solution, Note
FROM AdminView
WHERE (Status = @status)
ORDER BY CallLoggedDT
END
ELSE IF (@DepartmentName <> null) Begin
SELECT CallNumber AS [Call Number], Problem, Solution, Note
FROM dbo.AdminView
WHERE (Status = @status) AND
(DepartmentName = @DepartmentName)
ORDER BY CallLoggedDT
end
when i run the 2nd half by itself it tells me to declare @status but not @departmentname. whats going on???
Chris Morton
STDEV() gives incorrect values with reasonable input.
I have a table filled with GPS readings. I've got a column LATITUDE (FLOAT) with about 20,000 records between 35.6369018 and 35.639890. (Same value to the first 5 digits of precision---what can i say, it's a good gps.)
Here's what happens when I ask SQL Server ("9.00.1399.06 (IntelX86)") to compute the standard deviation of the latitude:
// Transact-SQL StdDev function:
SELECT STDEV(LATITUDE) FROM GPSHISTORY
WHERE STATTIME BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
0
// Zero. ZERO??!?!!
//Let's re-implement Std Dev from the definition using other aggregate functions:
DECLARE @AVERAGE FLOAT;
SELECT @AVERAGE = AVG(LATITUDE) FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
SELECT SQRT(SUM(SQUARE((LATITUDE - @AVERAGE)))/COUNT(LATITUDE)) FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
6.03401924005392E-06
// That's better. Maybe STDEV is using fixed point arithmetic?!?
SELECT STDEV(10 * LATITUDE)/10 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
4.77267753808509E-06
SELECT STDEV(100 * LATITUDE)/100 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
1.66904329068838E-05
SELECT STDEV(1000 * LATITUDE)/1000 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
8.11904280806654E-06
// The standard deviation should, of course, be linear, e.g.
DECLARE @AVERAGE FLOAT;
SELECT @AVERAGE = AVG(LATITUDE) FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
SELECT SQRT(SUM(SQUARE(100*(LATITUDE - @AVERAGE)))/COUNT(LATITUDE))/100 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
6.03401924005389E-06
// Std Dev is a numerically stable computation, although it does require traversing the dataset twice.
//
// This calculation is not being done correctly.
//
// Incidently, SQRT(VAR(Latitude....)) gives 4.80354E-4, which is also way off.
I will redefine STDEV to use a stored procedure similar to the above, but the algorithm used to compute VAR, STDEV etc should be reviewed and fixed.
-Rob Calhoun
I have a query which is returning a different result set when it is run against identical tables in 2 different environments.
The query is like:
Select
F.LicenseeID, IsSpecialLicensee
from FactTable F
left join View_SpecialLicensee SL on F.LicenseeID = SL.LicenseeID
The Create Statement for the view is like
Create View [dbo].[View_SpecialLicensee]
as
Select LicenseeID, LicenseeName, IsSpecialLicensee = 1
from DimensionLicensee
where LicenseeName like '%ibm%'
or LicenseeName like '%cisco%'
or LicenseeName like '%hp%'
In my test environment, I get the query result I expected:
LicenseeID, IsSpecialLicensee
1 , 1 - (where LicenseeName = 'IBM')
2, null - (where LicenseeName = 'Juniper')
3, 1 - (where LicenseeName = 'Cisco')
4, null - (where LicenseeName = 'Microsoft')
5, null - (where LicenseeName = 'Oracle')
6, null - (where LicenseeName = 'Apple')
In my production environment, I get the following query result:
1 , 1 - (where LicenseeName = 'IBM')
2, 1 - (where LicenseeName = 'Juniper')
3, 1 - (where LicenseeName = 'Cisco')
4, 1 - (where LicenseeName = 'Microsoft')
5, 1 - (where LicenseeName = 'Oracle')
6, 1 - (where LicenseeName = 'Apple')
Ideas as to what changed gratefully received.
FYI the production environment which returned the 2nd dataset is SQL2000, I have got the result I expected in both SQL2000 and SQL2005 development environments.