I want to write an SQL statement which returns matching values but ignores the first 2 dig

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

try use Substring in SQL statement|||

I'm not sure if you want this to just a straight up query or something dynamic

If you want it to be passed into a query, you can do
string criteria = "11/11/1234";
"SELECT ... (File_No = " + criteria.Remove( 0, 2) + ");

Use Parameterized query and not the exact example above.

If you want just a straight up query use LIKE
SELECT... (File_No LIKE '%/11/1234')

|||

You can use something like this:

DECLARE

@.lcModifiedIDvarchar(10),

@.liMaxFiedlLength

asint,

@.liStartPoint

asint

SET

@.liMaxFiedlLength= 100

SET

@.liStartPoint= 4

SET

@.lcModifiedID=substring('11/11/1234/',@.liStartPoint,@.liMaxFiedlLength)

print

@.lcModifiedID

SELECT

Add_Date, File_NoFROM(SELECT'12/11/1234/' File_No,'aa'Add_date)aaWHEREsubstring(File_No,@.liStartPoint,@.liMaxFiedlLength)= @.lcModifiedID

Thanks

JPazgier

|||

I Have a textBox named TextFile which is where the user enters the file number which will be in the format of 11/11/1234 and then there is a button with an on click event to trigger my SQL query

I have an SqlDataAdapter with the first parameter set as @.FileNo

I want the query based entirely on the the value of textBox

I am currently doing it like this

SELECT Add_Date, File_No FROM dbo.File_Storage WHERE (File_No = @.File_No)

But this only returns exact matches and as i say i need to return values that ignore the first 2 digits contained in @.File_No

Thanks for such a quick response

|||

I am pretty sure you can change "File_No = @.File_No" to "File_No LIKE @.File_No"

Then when you declare the value of the parameter:
.Value = "%" + TextFile.Text.Remove( 0, 2);

|||

Yes, you are right maybe it will work but remember that LIKE structure is very slow and designed for another purposes.

Thanks

JPazgier

I need Optimised duplicate finding query

I have a query which is like this
Select field a, field b,field c, field d from table1 where field d not in
(select distinct field d from table 2)
Table 1 has 35000 records
Table 2 has 12391876 records
Field d is of type varchar.
If there is any duplicate to be found in table 2 then the insertion from
table 1 to table 2 will not happen.
I need this query to be get optimised.Hi
DECLARE @.rowcount INT
SELECT Field1,COUNT(*) FROM Table2
GROUP BY Field1
HAVING COUNT(*)>1
SET @.rowcount =@.@.ROWCOUNT
IF @.rowcount >0 --Do exist duplicate rows
.........
"Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
news:CDCF5DF6-52FA-4D80-AAED-1F3803720972@.microsoft.com...
>I have a query which is like this
> Select field a, field b,field c, field d from table1 where field d not in
> (select distinct field d from table 2)
>
> Table 1 has 35000 records
> Table 2 has 12391876 records
> Field d is of type varchar.
>
> If there is any duplicate to be found in table 2 then the insertion from
> table 1 to table 2 will not happen.
>
> I need this query to be get optimised.
>|||I understand that the below query is used for finding out a duplicate value
in table2.
Note : field d contains <FILE NAME> . if there are 20 records in a file then
20 records will have the same filename and so on. A group of files will be
imported to the temporary table (table 1). Before inserting it to the main
table (table 2) a check is done whether that file is existing in the main
table. If it is existing then the insertion process will not done in order t
o
avoid duplicate file being entered into the main table.
We use the below said query given by me for this task.
So I need a optimised query.
"Uri Dimant" wrote:

> Hi
> DECLARE @.rowcount INT
> SELECT Field1,COUNT(*) FROM Table2
> GROUP BY Field1
> HAVING COUNT(*)>1
> SET @.rowcount =@.@.ROWCOUNT
> IF @.rowcount >0 --Do exist duplicate rows
> ..........
> "Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
> news:CDCF5DF6-52FA-4D80-AAED-1F3803720972@.microsoft.com...
>
>|||> So I need a optimised query.
CREATE indexes to optimize your query
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...ing_indexes.asp
"Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
news:2B4DD3B0-5BFE-4984-9726-9036457E8EA1@.microsoft.com...
>I understand that the below query is used for finding out a duplicate value
> in table2.
> Note : field d contains <FILE NAME> . if there are 20 records in a file
> then
> 20 records will have the same filename and so on. A group of files will be
> imported to the temporary table (table 1). Before inserting it to the main
> table (table 2) a check is done whether that file is existing in the main
> table. If it is existing then the insertion process will not done in order
> to
> avoid duplicate file being entered into the main table.
> We use the below said query given by me for this task.
> So I need a optimised query.
> "Uri Dimant" wrote:
>|||You might try using a not exists instead of not in. Not in will select all
12,391,876 rows from table2 then order them and summarize them, which
requires a fiar amount of in memory processing. The Not exists will check
table2 for each row in table1, meaning a maximum of 35,000 lookups. Note,
this will be faster if you have an index on table2.fieldd, but may be much
slower is this index does not exist.
Select fielda, fieldb,fieldc, fieldd from table1 t1 where not exists
(select 1 from table2 t2 where t2.fieldd = t1.fieldd)
"Cynthia" <Cynthia@.discussions.microsoft.com> wrote in message
news:CDCF5DF6-52FA-4D80-AAED-1F3803720972@.microsoft.com...
> I have a query which is like this
> Select field a, field b,field c, field d from table1 where field d not in
> (select distinct field d from table 2)
>
> Table 1 has 35000 records
> Table 2 has 12391876 records
> Field d is of type varchar.
>
> If there is any duplicate to be found in table 2 then the insertion from
> table 1 to table 2 will not happen.
>
> I need this query to be get optimised.
>

I need help with my query

Hi Y'all,

I want to get all database-names from my server, i do that like this:

select name, dbid from sysdatabases => from my master database

Now i want to add a "where"-clause to select only those databases that have the table "TASKS".

Can someone help me out?

THanks

Hi,

If you are trying to get the list of tables, database name etc. in your ASP.Net application then have a look at it.http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbconnection.getoledbschematable.aspx

Thanks and best regards,

|||

this doesn't seem to work:

SET @.sSql = 'select * from ' + @.databasenaam + '.dbo.sysobjects where name like '''tablesearchnaam''' '

if exists(EXEC sp_sqlexec @.sSql)

|||Have you tried this already?

SELECT*FROM sysobjectsWHERE [name]LIKE'%tasks%'

This can also be done without using a stored procedure...

IFEXISTS(SELECT*FROM sysobjectsWHERE [name]LIKE'%tasks%')
DO STUFF

|||

If you just want tables you might want to restrict the type to 'U'.

SELECT*FROM sysobjectsWHERE [name]LIKE'%tasks%' AND Type = 'U'