I would like to search MySQL table for names by selecting the first letter of the name.
eg: SELECT * FROM names WHERE lastName "begins with" M (or Mi, etc)
Right now if I use LIKE I get all names that contain "M".
Thanks.
PeterM
I have a large website with over 100,000 images and the location of the images are stored in a column (img_url) as below:
/images/imagename.jpg
Because all these images are stored in the same folder it is hard to manage so we want to store each image under a directory based on the 1st letter of the image name, ie:
/images/a/aimage.jpg
/images/b/bimage.jpg
I can automate the physical move of the images into the correct directory but I need SQL update query that will update each column based on the 1st letter of the image.
So:
/images/aimage.jpg
/images/bimage.jpg
Updated to:
/images/a/aimage.jpg
/images/b/bimage.jpg
I want to update Flag column in second table based on the Adder names.
If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True.
If the Application has more the one AIX and Adder names are diferent then the flag would be null.
APpName OS Adder
App1 ||| Windows|||Null
App1 ||| Linux |||UDB
App1 ||| AIX |||UDB
App1 ||| Linux |||Sql
App2 ||| AIX ||| UDB
App2 ||| Windows||| UDB
App2 ||| Linux ||| UDB
App2 ||| AIX ||| UDB
OUTPUT SHOULD BE LOOK LIKE BELOW
APpName OS Adder Flag
App1||| Windows|||Null|||null
App1||| Linux |||UDB |||null
App1||| AIX |||UDB |||null
App1||| Linux |||Sql |||null
App2|||AIX ||| UDB|||TRUE
App2|||Windows||| UDB|||TRUE
App2|||Linux ||| UDB|||TRUE
App2|||AIX ||| UDB|||TRUE
I am trying to query the SharePoint database to get a list of how many times a table name is being used in our SSRS reports.Here is my current query:
DECLARE @TEXTTOSEARCH AS VARCHAR(200)
SET @TEXTTOSEARCH = 'tablenamehere'
;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd)
[code]...
This works for some tables since the names are unique and I can just do a search for that. However, some of the table names are simple and can also be used as aliases.if there was some way to get the list of table names easier.
Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
Thanks Ross
Hello!I want to get the first letter in a sql-query and compare with a QueryString.This is the pricip in ASP Classic:Select * From Dictionary Where Left(_Name, 1) = Request.Querystring["Letter"]I think you'll understand.Thanks.
View 1 Replies View Related
Hi,
I have a table as follows
Table Master
{
Id varchar(20),
Cat1 datetime,
Cat2 datetime,
Cat3 datetime,
Cat4 datetime
}
and the data in the table is as follows
Table Master
{
Id cat1 cat2 cat3 cat4
-----------------------------------------------
1 d11 null d13 d14
2 d21 d22 d23 d24
3 NULL d32 d33 d34
4 d41 d42 NULL NULL
}
I want to retrive column names and its values wheb the ID matches to some value.
Can any one please let me know how to do this?
Thanks alot
~Mohan
I have a scenario where I need to move a series of files from within a directory of many files. The files follow no nameing convention and are more of less random. However the file names never change from week to week. I tried various different options in a 'file system task', no go.
Any ideas on how to move only a list of files?
or
can I load only specific files into a 'Foreach Loop container' from a certain directory. I tried delimiting file names in the file source, that did not work.
Either way can work,
Thanks
I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:
select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank
[code]....
Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:
GDR (nvarchar(255), null)
GDR Percentile Rank (nvarchar(255), null)
GDR PGS (nvarchar(255), null)
GDR Rank Number (nvarchar(255), null)
PMPM (nvarchar(255), null)
[Code] ....
These are imported from an Excel Workbook so that's why all the columns with spaces for now.
Dear Sir,
I am building a website some what like B2B portal using asp.net and access database. I want to provide a search facility to the user through which they can search products in our database.
Can you provide me a strong SQL Query for that. Or is there any other way of doing that.
Please help me.
Thanking You
Suraj
i have a column of dates.
i make an index or key for this column so that
i can perform binary search i hope.
If the search is not succesful how do
i get the query to return the two dates
surrounding the date i was searching for.
Hello Gang,
I have a strange problem that I haven't dealt with before.
I need to execute a piece of code based on date ranges. If the date range is:
Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
-----------------------------------------------------------
Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y
Can anyone help me with this code. I am having a SCD (Stupid Coder Day) and can't seem to do anything that is scalable - like accounting for leap years (Feb issue) and the fact that Scenario # 2 above is between 2 different calender years.
Your help is much appreciated.
JJOSHI
Hello Gang,
I have a strange problem that I haven't dealt with before.
I need to execute a piece of code based on date ranges. If the date range is:
Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
-----------------------------------------------------------
Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y
I am trying to automate a report. The report is supposed to generate a result that will differ based on the date ranges going into the future. E.g.
[1]. If the run date of the report is between '2/1/20xx' and '7/31/20xx' display <ABC> or
[2]. If the run date of the report is between '8/1/20xx' and '1/31/20xx' display <PQR>
In example # 2. I am moving from one year to the next (July to Dec and the one extra month of Jan). So for example, if the guy runs the report between August of 2008 and January of 2009, display <PQR>.
How do I achieve both # 1 & 2 above in a code? Does this explain better.
Hi,
How do you do a case sensitive searching without having the database case sensitive or is this even possible. There are times we want to perform case sensitive searching and case insensitive searching.
I found this article, but is that the suggested way. seems a bit bad, but if it is the way I am willing to use it.
http://sqlserver2000.databases.aspfaq.com/how-can-i-make-my-sql-queries-case-sensitive.html
Thanks,
Matt
hi;
i want to get some results for new my stored proce is below;
create Procedure HaberleriGetir
@Kelime varchar(50),
@tarih1 smalldatetime,
@tarih2 smalldatetime,
@KatID int,
@lang char(5)
as
if @lang = 'Hepsi'
select * from Haberler where HKatID = @KatID and Metin like %@Kelime% or Baslik like %@Kelime% and Tarih between @tarih1 and @tarih2
else
select * from Haberler where HKatID = @KatID and lang = @lang and Metin like %@Kelime% or Baslik like %@Kelime% and Tarih between @tarih1 and @tarih2
incorrect near @Kelime....
and i can't get a result between two dates like @tarih1 & @tarih2
In my asp.net web application I used two textboxes for date1 and date2 to search records I have used two variable in my stored procedure @OrderDate1 datetime,@OrderDate2 datetime,for todays record (values for date1 and Date2)set @OrderDate1='09/20/2007 12:00 AM';set @OrderDate2='09/20/2007 11:59 PM';for records between (values for date1 and Date2)set @OrderDate1='09/20/2007 12:00 AM';set @OrderDate2='09/24/2007 11:59 PM';I have to search the todays records OR records between these two days (OrderDate >= @OrderDate1 ) AND (OrderDate <= @OrderDate2) It
works fine in my local server>>It finds the todays records by
ginving same date (@OrderDate1 and @OrderDate2) and records between two
dates but it does not work for sql server which is outside india ...if it is problem of date format how i can solve that.. Thanks
I am interested in creating a query that will test if a value is the same in a particular field.
For example, if the value is "0", or "000", or "000000" or "333", "444444", I would like to extract it. Otherwise omit the value.
I need to move specific files from a server to another server on a monthly basis. There are hundreds of files that are in the source directory and I need to move approximately 40 of those to the destination server. I would like to easily add or delete the file list as needed. I have seen where several variables were created for for each file name (and one for the path) and the ForEach Loop would go through them. With 40 or more I was thinking that I could make a connection to an Excel spreadsheet or text file with a record for each file name and read in and and move to the next record and make that value become the content of a "FileName" variable. Then if I wanted to add another file name I could just add another record to spreadsheet/text file or remove and the package would handle automatically....
View 10 Replies View Relatedhi i have one field in employee table
where the field name is employeename
where firstname + ' ' + lastname is stored in that field
how can i break them in 2 pieces so that i can store in 2 different fields and some reacords has got
firstname+ ' ' + middlename+ ' '+ lastname and i want to store them in temp tables how can i do that.
Using VB5|6/ADO/MSSQL6.5. I want to get the names of tables in a query. I.e. if query is "Select Name, Title from Employee inner join job on employee.empid = job.empid" I want a way to get "Name, Title"
In VB 3 we used the VBSQL.VBX which had 2 functions for this: SQLTabCount and SQLTabName.
Thanks in advance
DP
Hi,
I have a database that have two handred tables.I want to get all the table name list.
Would you please help me if you know how to do this (query or build-in stored procedure).
Thanks
Mark
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID
FROM tableU u
LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
Hi, I am trying to get all the databases from MS SQL into a dropdownlist. However, I am not sure how to do that in query ... is there a way to retrieve all databases name using a query in MS SQL? Thanks.
View 1 Replies View RelatedIs it possible to use variable name to dynamically define a query in a stored procedure? EX:
@Column = 'COUNT(*)'
@Category = 'Products'
@Table = 'Items. + @Category
SELECT @Column
FROM @Table
I am trying to retrieve the user names of all users within a database. The problem is, that I have only created a login within SQL 7.0 for the NT group that the users belong to.
As such, when I try to query the syslogins table I only get the NT group.
Is there any way of retrieving the users that belong to this NT group.
Any help or suggestions on this would be greatly appreciated.
Thanks,
Karl
I want a query that returns only the column names
View 4 Replies View RelatedHi,
How canI get all field names in a table using sql query? Now I am not interested in the data in this table, what I am interested in is just the schema, i.e. all field names. Thanks.
I have a vb.net page that I need to display a list of employees who work in a specific office, based on a MatterID passed in a query string. But, I don't know how to get a value returned from one sql statement into a second. Here's what I'm trying to do...
From the QueryString, we know that the MatterID = 4 ( xxx.aspx?MatterID=4)
Knowing that the Matterid=4, I query the database to get the OfficeId for that MID (Select OfficeID from tMatter where Mid=4) ~This returns an OfficeID of 6
So, then I need to do another query to get the employees where OfficeID = 6 (Select EmployeeID from tEmployees where OfficeID = 6)
How do I do these in one query, or how do I use the Calculated Value for the OfficeID in the 2nd statement?
linking two tables together to get an end result
find below the code i have used
The first part of the query provides me with the info i need
SELECT sub.*,
case when rm_sales_band = '2M to 4M' then 'Kirsty' else RM end as rm
into #rmtmp
[Code].....
Hello All,
I have been trying to get this code work, but I could not. Every thing seems going well. However, The result of running the sql query is strange. It shows the field names twice.
Eg:) if you have a table called "newtable" that has two fields[Custnumber, Custname], you will get somthing like this [Custnumber, Custname Custnumber, Custname]. I have tried many times, but I couldn't fix it.
Sub Page_Load(sender As Object, e As EventArgs) handles Mybase.Load
if not page.Ispostback then
try
Sqlconnection = New Sqlconnection (connectionString)
querystring = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNs
WHERE TABLE_NAME = 'Newtable'"
SqlCommand = New SqlCommand(queryString, Sqlconnection)
SqlConnection.Open
dataReader = SqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
while dataReader.Read()
Tablefields_txt.text += dataReader.Getstring(0) & ", "
End while
catch ex as Exception
msgbox("An error has occured: " + ex.Message,0, "Error Message")
finally
SqlConnection.Close()
End try
End if
Any help , please
I have two different tables... one for all Staff, and another for all Temp Staff. I need both to output to a datagrid, and so I need to grab both tables from a SQL query to output to my datagrid, but I can't seem to get the logic right for it to work. Can someone give me some suggestions on why my results are blank when I'm running this query? I thought a simple join would allow both sets of identical column names to coexist in peace...SELECT TOP 100 PERCENT dbo.StaffDirectory.UserName, dbo.StaffDirectory.LastName, dbo.StaffDirectory.FirstName, dbo.StaffDirectory.Dept, dbo.StaffDirectory.Title, dbo.StaffDirectory.EMail, dbo.StaffDirectory.LocationFROM dbo.StaffDirectory INNER JOIN dbo.TempStaff ON dbo.StaffDirectory.Location = dbo.TempStaff.Location AND dbo.StaffDirectory.EMail = dbo.TempStaff.Email AND dbo.StaffDirectory.Title = dbo.TempStaff.Title AND dbo.StaffDirectory.Dept = dbo.TempStaff.Dept AND dbo.StaffDirectory.FirstName = dbo.TempStaff.FName AND dbo.StaffDirectory.LastName = dbo.TempStaff.LName AND dbo.StaffDirectory.UserName = dbo.TempStaff.UName AND dbo.StaffDirectory.MDNo = dbo.TempStaff.MDNoIs something wrong here? It just doesn't work =(Any suggestions would be really appreciated.Thank you
View 5 Replies View RelatedI'm looking for a query that will return all index names, the table the index is on and the columns in the index...
View 1 Replies View Relatedhere is a query
Code:
select count(d) from (select distinct left(name,9) as d from bscs where region like 'n1' and name like 'Alc_%')
what i am trying to do is to get count of the names, having distinct letters in left 9 positions and some conditions too. How to do it?