How To Query For A Column Value That Contains Dashes
Oct 12, 2007
Hi,
I have a large table with a 'datetime' column that has date and time
values in it. The data is in this format:
2007-10-02 09:54:00.000
The table is called 'profile' and the column 'msgdate'
I want to return only rows that match a specific date. So far I have
the following query working:
select * from profile where msgdate like '%2007%'
This returns all rows that start with '2007'. However I cannot seem to
ge the syntax that will allow me to return a specific date, e.g.
2007-10-02
I have researched this, trying all sorts of queries with escape
characters/sequences because of the dash character, but I cannot get
it to return anything. Most of my queries have ran without error, its
just that no data is returned.
James
View 2 Replies
ADVERTISEMENT
Jul 23, 2005
Hi,I'm having trouble running the following query:select * from message where text_body like ' ----------%'ie, five spaces followed by at least ten hyphens. The query doesn'tcomplete, so eventually I cancel it. If I remove the hyphens from thequery ("... like ' %'") then it runs fine (though it doesn't findthe correct data).Am I confusing SQL Server by using a wildcard or regular expression?(I'm using SQL Server 2000 - 8.99.760).Thanks in advance for any helpRichard
View 5 Replies
View Related
Jul 20, 2005
Hi all,I have a table in the DB which holds ISBN book nubers .. which, as you knowhave dashes (examlple: 0-98765-8-9).When I run a query from an ASP page to find the number, it doesn't returnanything:sISBN = "0-98765-8-9"OpenSQL(select URL from ISBN_Redirect where ISBN=" & sISBN )Can you give me a hind of what I am doing wrong?Thanks so much in advance.S
View 1 Replies
View Related
Apr 8, 2008
Hi!
I run a query that returns strings that look like this: 08-07, 06-07,04-05 etc. But when I copy the query outuput from Management Studion in SS2005 into Excel 2003, strings get converted into custom dates such as 8-Jun, 9-Jul, 4-May etc in Excel. Is there anthing I can do in T-SQL to prevent this conversion? The strings are set as char(5) datatype in a T-SQL view.
Thanks.
View 8 Replies
View Related
May 30, 2008
Can anyone help with this. IF i have a ssn like 123459636 and i want to insert dashes into it how would i do that. Results would be 123-45-9636
The second problem is that some of the ssn dont have zero in them so some look like 1234567 i would have to pad with zero then trim from the right to 9 digits then insert the dashes not sure how to do that
results would be 001-23-4567
View 7 Replies
View Related
May 29, 2008
Hi,
In a extract that i have the SS# is as 123-45-6789 how do i remove the dashes in the string and have the final expression of 123456789.
thanks
Karen
View 3 Replies
View Related
Aug 8, 2007
when i do a bulk insert with dashes (i.e. one - two), i get the character u with two dots above it ( Ü ) in place of all the dashes? can't seem to figure out why. the field terminator is a comma ( , ).
i'm inserting just text with dashes into char(75) field.
can someone explain and provide a solution? i would greatly appreciate it!
my script is quite simple, maybe i'm missing something.
Code Snippet
bulk insert testtable
from '\localc$adjust.txt'
with (fieldterminator = ',')
thanks!
View 1 Replies
View Related
Nov 8, 2006
Ok. Here is a weird problem.
I have a package which contains a variable with the value of a UNC sharename. The hostname in the share contains dashes.
I am trying to create a SQL Agent entry to execute said package. Because the package is now in production I want to pass in a new value for the sharename variable. I use the SQL @@SERVERNAME variable to build the package command. (Notice the BlotterUploadFolder variable at the end.)
SELECT @command = N'/FILE "\' + @@SERVERNAME + 'PackagesMiddleOfficeReconcilliationImportManualBlotter_AccountTransfers.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /CONNECTION MiddleOfficeDb;"Data Source=' + @@SERVERNAME + ';Initial Catalog=MiddleOffice;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /SET package.variables[BlotterUploadFolder].Value;\' + @@SERVERNAME + 'ManualBlottersUploads'
If I print the @command variable right after setting it, I get what I expect.
/FILE "\PRD-NY-DB-01PackagesMiddleOfficeReconcilliationImportManualBlotter_AccountTransfers.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /CONNECTION MiddleOfficeDb;"Data Source=PRD-NY-DB-01;Initial Catalog=MiddleOffice;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /SET package.variables[BlotterUploadFolder].Value;\PRD-NY-DB-01ManualBlottersUploads
If I cut-paste this into cmd window and execute it with DTExec.exe the package works fine. But when I run the schedule task it causes an error. When I go into the Job Properties dialog to look at the step, I see the value (in the "Set values" table) set to \PRD instead of \PRD-NY-DB-01.
If I modify the SELECT command above to put double-quotes around the value, the value is correct in the Job Properties dialog but causes an error when I exec it with DTExec.
/FILE "\PRD-NY-DB-01PackagesMiddleOfficeReconcilliationImportManualBlotter_AccountTransfers.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /CONNECTION MiddleOfficeDb;"Data Source=PRD-NY-DB-01;Initial Catalog=MiddleOffice;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /SET package.variables[BlotterUploadFolder].Value;"\PRD-NY-DB-01ManualBlottersUploads"
causes the error
Option "-NY" is not valid.
Hopefully somebody has an idea of what is going on.
Thanks.
- Jason
View 3 Replies
View Related
Jul 23, 2005
Hello,I have had a request, one of our tables is used as a report and I have beenasked that all fieldnames for months have dashes in them,like Jan-05 instead of Jan05 and so on...Now what we have is a CURSOR which loops through all values in another tablegenerating these fieldnames, like 'Jan-05', Feb-05' etc..Then the table definition is modified so these months display as fieldnames.That is fine,Except later it calls another stored procedure which inserts values fromanother table into this one.INSERT INTO TableNameSELECT *FROM TablenameObviously this is failing because the newly amended table now has dashes inthe fieldnames now.Now as the fieldnames are created dynamically I cannot simply do INSERTTablename ([Jan-05], [feb-5]) etc, as one would normally do,How could I do this bearing in mind its not always going to be 12 months weuse,Basically I am asking how can I modify this INSERT statement so it takesinto account the fieldnames with dashes in themBearing in mind I don't know what the fieldnames are called as they arecreated dynamically by a CURSOR in a different routine.And its not always 12 months.have you any ideas?and the dashes are requiredJayne
View 4 Replies
View Related
Jun 29, 2007
Using the "set values" tab, I am trying to set the values of package variables to strings that contain spaces and/or dashes. If I run the package using the integrations services server, it runs perfectly. If I schedule the package on SQL Server agent, I get the following error.
"Executed as user: GBPSQLServer. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Option "-restofstring" is not valid. The command line parameters are invalid. The step failed."
Looking at the command line generated by the SQL Agent interface, the value is quoted using double quotes like this:
/SQL "PackageName" /SERVER SQL1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "Package.Variables[LogFolder].Value";"\remoteservernameinetpub he-restofstring
estofstring" /REPORTING E
It seems that the command line parser is not recognising the quotes, or maybe the slashes are throwing it off, or sql server agent is doing something funny to the command line before it executes it.
Anybody got any ideas?
View 1 Replies
View Related
Sep 14, 2015
I have created a job which executes a procedure which executes and creates logs of StartTime and EndTime and records affected. The last step of the job sends mails to recipients from the logs table. Though I get the info correctly I get all annoying dashes for 4-5 lines between each line of message. How to remove the dashes.
Below is the code i use to send mails.
EXEC msdb.dbo.sp_send_dbmail
@profile_name='SQL Mail Service',
@recipients='biscribbler@gmail.com',
@copy_recipients='biscribbler@gmail.com',
@subject='SOFT SCRUB Completed for LiveDB',
[Code] ....
View 2 Replies
View Related
Apr 10, 2006
I have imported a package using SSIS into the Maintenance Plan folder from another server.
It imports fine, but when i Modify the package then click save I get the following error : 'Guid should contain 32 digits with 4 dashes'.
This has been reported on other forums without resolution.
Pete
View 17 Replies
View Related
Jul 20, 2005
Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy
View 5 Replies
View Related
Sep 22, 2015
-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!!
-- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"
set nocount on
go
if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1
if object_id('tempdb.dbo
[code]....
This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?
View 2 Replies
View Related
Nov 15, 2007
do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like
SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name
FROM...
WHERE ... and calcd name in (@variable)
View 1 Replies
View Related
Feb 25, 2012
When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.
result in Excel 2003 SQL OLE DB query
col-A col-B col-C
values...
Result with Query Analyzer and VBA Macro
col-c col-B col-A
values...
View 3 Replies
View Related
Oct 23, 2014
I need to name make the name of a column the same as the name of a table from the result of a sql query.. here is the assignment question below..I can't figure out how to get the name of the table to be inputed as the column name..
Write a script that uses dynamic SQL to return a single column that represents the number of rows in the first table in the current database. The script should automatically choose the table that appears first alphabetically, and it should exclude tables named dtproperties and sysdiagrams. [highlight=#ffff11]Name the column CountOfTable, where Table is the chosen table name.[/highlight]
Hint: Use the sys.tables catalog view.
I can figure out the rest. and actually have alredy done it, but i cannot figure out how to do the part that is highlighted above. I looked at lots of things on google to figure it out but no luck..Can some just give me some directlon or an example..
View 14 Replies
View Related
May 9, 2015
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB 123
colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
[Code] ....
View 4 Replies
View Related
Jun 3, 2004
I'm having a bit of a trouble explaining what I'm trying to do here.
I have 3 "source" tables and a "connecting" table that I'm going to use
tblContacts - with contactID, ContactName etc
tblGroups - with GroupID, GroupName
tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)
They are related in a table called
tblContactsGroupConnection - with ContactID, GroupID and SubGroupID
One contact can be related to many subgroups.
What I want is a list of all contacts, with their IDs, names and what groups they are related to:
ContactID, ContactName, [SubGroupName1, SubGroupName2, SubGroupName3]
ContactID, ContactName, [SubGroupName1, SubGroupName3]
ContactID, ContactName, [SubGroupName3]
I'm sure there's a simple solution to this, but I can't find it. Any help appreciated. :)
Kirikiri
View 1 Replies
View Related
Jul 9, 2007
Can someone tell me how to sum values in a column from a resultant Query. For example, consider this query which in partcalls a view named "Rpt_OverallIndMarket"
select * from Rpt_OverallIndMarket order by MarketId,Year,Quarter
The resultant query from the previous query will contain a column named TotalSquareFeet. How do I sum all of the TotalSquareFeet values inthat column?
View 4 Replies
View Related
Apr 2, 2007
Hi, I'm pretty sure that I've missed something obvious but I can't think of what it is, I need to get rows where "forid" is 0 and 1 but I can't think of how to do it, I tried:
SELECT * FROM `events` WHERE `forid` = '0' AND `forid` = '1'
But that query isn't correct.
Appreciate any help, thanks.
View 4 Replies
View Related
Nov 2, 2006
What's the best way to include an amount sum in a query if I also need the individual amounts? For example, I need the following columns:
order number
order amount
total amount
I tried using "with cube ", but the total number of columns in the query exceeds the allowable limit of 10.
View 11 Replies
View Related
Feb 18, 2008
My XML column is like below.
<rs:CheckPointExpressIDNotRequired xmlns:rs="urn:release-schema">
<rs:IsJavaRuling>True</rs:IsJavaRuling>
<rs:IsGeoPolitical>True</rs:IsGeoPolitical>
<rs:IsConsentDecree>True</rs:IsConsentDecree>
<rs:HasThirdPartyBits>False</rs:HasThirdPartyBits>
<rs:MandatoryLicenseAgreement>Verified</rs:MandatoryLicenseAgreement>
</rs:CheckPointExpressIDNotRequired>
My query is below.
SELECT
ref.value('IsJavaRuling[1]', 'varchar(36)') as [IsJavaRuling]
FROM REQUESTCOMPLIANCE CROSS APPLY xml.nodes ('//CheckPointExpressIDNotRequired') R(ref)
order by lastmodifieddate desc
My query runs successfully but I am not getting any results back however there are many rows to be returned. Can you tell me what is wrong with the query?
Thanks,
Koti
View 7 Replies
View Related
Feb 8, 2008
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
View 9 Replies
View Related
Sep 22, 2006
Is it possible to use a column name variable in a Select Statement for a column name?For example I have a dropdown with FName,LName,Phone and a text box. The user can select the field to search and the criteria will go into the text box. The problem is the select doesn't like a variable for the field name. I have tried both a standard variable and a Case statement (see below). This is being used in a Stored Procedure with MSSQL. The actual select is much more complicated than this but it gets the point across. Thanks for your help in advance@Field as varchar( 50),@Value as varchar (50)SELECT *FROM customersWHERE @Field = @ValueORSELECT *FROM customersWHERE CASE WHEN @Field = 'Fname' THEN Fname = @Value END, CASE WHEN @Field = 'Lname' THEN Lname = @Value END, CASE WHEN @Field = 'Phone' THEN Phone = @Value END;
View 1 Replies
View Related
Apr 3, 2007
I have 2 tables, my vehicle data table and my config table. I need a
query to join them by a datarow and a data column. Heres my tables...config table--------------------id name type--------------------1 make varchar2 model varchar3 color varcharveh table--------------------------id make model color--------------------------1 chevy s10 white2 ford ranger silver2 chevy blazer brownrecordset needed for veh.id=1---------------------------id name type value---------------------------1 make varchar chevy2 model varchar s103 color varchar white Thanks for any helpRyan
View 1 Replies
View Related
Nov 15, 2007
Hi I have the following problem. I am trying to get some data from a database which matches the name in a session from a previous page:e.g. SqlCommand menubar = new SqlCommand("Select pernme from Person where pernme = " + (string)Session["tbname"], sqlConn); SqlDataAdapter dataAdapter5 = new SqlDataAdapter(); dataAdapter5.SelectCommand = menubar; DataSet dataSet5 = new DataSet(); dataAdapter5.Fill(dataSet5); DataTable selcartest4 = dataSet5.Tables["table"]; if (selcartest4.Rows.Count != 0)The session is called tbname and in that session is a users name however insetad of doing the nornal thing and retrieving the data in the sql database table matching that name it comes up with the following error message:System.Data.SqlClient.SqlException: Invalid column name 'jamie'this is weird as the 'jamie' is the name in the session from the previous page and in fact not a column name at all the column name is pernme I am totally stuck any help would eb great thanksJ
View 2 Replies
View Related
Jan 11, 2005
Simple example would look like that in MS SQL
SELECT 'a' AS (SELECT language_name FROM language WHERE language_id = 1)
So that the display is
English
a
as we assume that
SELECT language_name FROM language WHERE language_id = 1
returns only English
I have tried to that with a variable but it does not work
declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as @that
LOL
I just tried another way as i was going to post
declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as "@that"
and it worked!
Posting anyway so people might answer with a better solution with no variable
Thanks a lot
Mordan
View 1 Replies
View Related
Aug 22, 2001
Hi all,
Can somebody tell me how I can pass column name of a table to a stored proc.
Here's something close to what I'm trying to do....
Declare @colname varchar(30)
set @colname = "TITLE"
select name, id
from contacts
where contacts.@colname = "TITLE"
Regards
Uday
View 2 Replies
View Related
Dec 22, 2000
I am trying to parse a query column that has both a city name and state code within it. What I would like to do is parse the queryed column and seperate them by the ",". Can someone please help?
example:
Select Name, Address1, Left(Address2, ",") as City, Right(Address2, ",") as State
From tblPersonalInfo
View 2 Replies
View Related
Mar 26, 2006
I am trying to develop this query in MSSQL but am having a problem with the syntax.
I don't know why but the query is breaking on 'Invalid Column Name A1'.
Here is my query.
Code:
SELECT
Groups.GroupID,
Sum(Stages_On_Route.Distance) AS Miles_Covered,
Groups.Group_Name
FROM Groups
INNER JOIN ((Route INNER JOIN Departure ON (Route.GroupID=Departure.GroupID)
AND (Route.RouteID=Departure.RouteID))
INNER JOIN Stages_On_Route ON Route.RouteID=Stages_On_Route.RouteID)
ON Groups.GroupID=Departure.GroupID
GROUP BY Groups.GroupID,
Groups.Group_Name
HAVING (((Groups.GroupID)="A1"));
View 1 Replies
View Related
Sep 8, 2006
is there a way to sql query via a spesific column number
'get from table A row 1 column 1' and then 'get from table A row 2 column 3'
and so on
thanx
View 3 Replies
View Related
Sep 22, 2004
I have a database with a zip code column. I want to flag all rows that have letters in that column (a-z). How could I construct the where clause?
Is there a better alternative to:
WHERE ZipCode LIKE '%a%' OR ZipCode LIKE '%b%' ...
View 5 Replies
View Related
