Finding Dashes/hyphens In A Column.
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't
complete, so eventually I cancel it. If I remove the hyphens from the
query ("... like ' %'") then it runs fine (though it doesn't find
the 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 help
Richard
View 5 Replies
ADVERTISEMENT
Oct 12, 2007
Hi,I have a large table with a 'datetime' column that has date and timevalues in it. The data is in this format:2007-10-02 09:54:00.000The table is called 'profile' and the column 'msgdate'I want to return only rows that match a specific date. So far I havethe following query working:select * from profile where msgdate like '%2007%'This returns all rows that start with '2007'. However I cannot seem toge the syntax that will allow me to return a specific date, e.g.2007-10-02I have researched this, trying all sorts of queries with escapecharacters/sequences because of the dash character, but I cannot getit to return anything. Most of my queries have ran without error, itsjust that no data is returned.James
View 2 Replies
View Related
Nov 27, 2006
there are 5 hyphens. want to find it one by one.
set:
select ('wopipwier-lmklsdje-sdgbre-;erlsl;k-wertwer-wieroiu')
result:
col1|col2|col3 ....so on
- - -
View 10 Replies
View Related
May 23, 2006
Hi everyone,
I have a sql quey that selectes phoneNumbers from the database. Problem is some phone numbers have hypens in it and some doesn't. Is there any way in sql so that I can remove hyphens from the phone numbers
some numbers are like this
213-456-9999
and some are
2136789999
Please let me know if this is possible.
Thanks.
View 2 Replies
View Related
Feb 16, 2004
I receive a file that will have hyphens between data items such as
123-aed-edr-45r-ui9
1-ed3-45r-rrr-98u
I need to split the values to load into a table that will hold the 5 separate data itens. The fields will always have the hyphens but could be different lengths. Any idea on a best approach to split this in tsql
View 2 Replies
View Related
May 2, 2008
Hi,
How to find if a column exists in Database Table ?
thanx in advance
View 6 Replies
View Related
May 19, 2008
Hi All,
How to find all the tables of a database containing a column . for example,how list all the tables of employee database having employeeid ?
Cheers,
Mathi
India.
View 6 Replies
View Related
Apr 30, 2007
I want to find columns name associated with view. For example; can you please help me to write query? I need to write for several tables.
TableName Column View
---------- -------- -------
Employee EmpId v_EmployeeDetails
Employee Firstname v_EmployeeDetails
Employee Lastname --
Employee SSN v_PersonalD
Employee DOB --
___________________________________________________
--just example
go
create view v_EmployeeDetails
as
select EmpId,Firstname from Employee
go
go
create view v_PersonalD
as
select SSN from Employee
View 5 Replies
View Related
Jul 23, 2005
Hi all,How do I find all tables containing a column (say a column includingthe string 'value')?ThanksBruno
View 2 Replies
View Related
Jul 20, 2005
I have a task to where I need to move a column from one table toanother. I want to be sure I update any view, stored procedure,trigger, etc. that references the column. I simply want a query thatwill report the related objects and then I will update them manuallybut before I go and try and figure out how to do this by querying thesys tables is there an sp_sproc that will do this?
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
Jan 13, 2008
I'm using SQL 2005 Server Management Studio -
I go to the Northwind database - new query window and paste this code, I just found on this forum, to find the Identity column:SELECT C.name AS Colname, UPPER(U.name) AS Coltype, C.status AS Cstatus, C.ColId, C.Id AS Cid, C.length, C.xprec, C.xscale FROM syscolumns C JOIN sys.tables O ON C.id = O.object_id JOIN systypes U ON C.xusertype = U.xusertype WHERE O.name = 'Fred' AND C.status = 128 ORDER BY C.colid SELECT C.name AS Colname, UPPER(U.name) AS Coltype, C.status AS Cstatus, C.ColId, C.Id AS Cid, C.length, C.xprec, C.xscale FROM syscolumns C JOIN sys.tables O ON C.id = O.object_id JOIN systypes U ON C.xusertype = U.xusertype WHERE O.name = 'Products' AND C.status = 128 ORDER BY C.colid
However, I get an error message saying:Invalid object name 'sys.tables'.
What might I be missing here?
View 3 Replies
View Related
Jan 7, 2014
Aim – Find the latest #Account.[Last_Post_Date] for the Fdmsaccountno and populate the latest date for that Parentid. In a new column Called “Parent Last Post Date"
Current results
FdmsaccountnoParentidLast_Post_Date
87802012188487823334288920140105
87802012788187823334288920140103
87802012888987823334288920131231
87870180988087823334288920131217
87802012088687823334288920131204
87823334288987823334288920131201
87870180888287823334288920131130
87802011588687823334288920131120
87875705088487823334288920131011
Desired results
FdmsaccountnoParentidLast_Post_DateParent Last Post Date
8780201218848782333428892014010520140105
8780201278818782333428892014010320140105
8780201288898782333428892013123120140105
8787018098808782333428892013121720140105
8780201208868782333428892013120420140105
8782333428898782333428892013120120140105
8787018088828782333428892013113020140105
8780201158868782333428892013112020140105
8787570508848782333428892013101120140105
My query is
select
#Account.Fdmsaccountno,
#Account.Parentid,
#Account.[Last_Post_Date],
from #Account
inner join [Dim_Outlet] on #account.FDMSAccountNo = [Dim_Outlet].FDMSAccountNo
where #Account.Parentid = '878233342889'
order by [Last_Post_Date] desc
View 3 Replies
View Related
Aug 12, 2015
Query :-
SELECT
MAX(TIME_VAL) AS Duration1 ,
MIN(TIME_VAL) AS Duration2 ,
-- VALUE OF LATITUDE CORRSPONDING TO MAX(TIME_VAL)
-- VALUE OF LATITUDE CORRSPONDING TO MIN(TIME_VAL)
(Select LONGITUDE from (select LONGITUDE , row_number()
[Code] ....
I am not getting Correct values for StartLongitude , EndLongitude. How can i achieve this to get the val of latitude corssponding to max/ min of time_val column.
View 3 Replies
View Related
Nov 21, 2007
Hi
I always seem to come back to dataset issues. Nonetheless, here is my problem:
I am retrieving data using a stored procedure that lists the number of bookings per hour and the revenue that generates also per hour. So, you enter a single date in the stored procedure, for example 2001-10-01 and it will bring back 24 rows corresponding to the 24 hours (obviously) and two columns, one displaying the number of bookings and one displaying how much revenue.
Pretty simple so far.
I have a report using SQL Reporting Services that allows users to enter two dates and therefore you get two tables with the relevant dates data. For example, table one has date 1's data and table 2 has as you'd expect date 2's data. Both tables use different datasets.
The reason why I am using different tables and different datasets is that the stored procedure I use only allows the use of one date at a time therefore, I use a second dataset to generate the second date's data.
The problem occurs when I want to calculate a difference between the two date's data, for example have another table that shows the differrence between table one and table two for bookings for each corresponding hour.
Anyone have any ideas as to how I could achieve this?
View 1 Replies
View Related
Feb 13, 2008
I have a table. In that table I have a list by student number that lists the entry dates into a particular grade. When trying to list only the first time entered, there is no unique way to identify one row from another other than the date. Is there a way to use max or min to only pull one date per student number? I have done a series of case when statements and I am able to get it down to 1 to 2 entries per student number, but I need to get it down to only 1 date per student number.
Thank you for your help
SELECT DISTINCT mx.stu_num,
CASE WHEN er.STU_NUM = ep.STU_NUM
THEN er.enterdater
ELSE CASE WHEN ea.STU_NUM = ep.STU_NUM
THEN ea.enterdatea
ELSE CASE WHEN eb.STU_NUM = ep.STU_NUM
THEN ep.enterdatep
ELSE eb.enterdateb
END
END
END AS entrydate
FROM dbo.mx AS mx LEFT OUTER JOIN
dbo.v_EntryDate9_R AS er ON mx.stu_num = er.stu_num LEFT OUTER JOIN
dbo.v_EntryDate9_P AS ep ON mx.stu_num = ep.stu_num LEFT OUTER JOIN
dbo.v_EntryDate9_A AS ea ON mx.stu_num = ea.stu_num LEFT OUTER JOIN
dbo.v_EntryDate9_B AS eb ON mx.stu_num = eb.stu_num
View 1 Replies
View Related
Jan 28, 2008
I am using Access 2007 and I have 10 columns of data.
I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.
I have used this
SELECT DISTINCT [SFR Rates All].Sorter, [SFR Rates All].SProgram, [SFR Rates All].S_Price, [SFR Rates All].Min_Loan, [SFR Rates All].Max_Loan
FROM [SFR Rates All];
But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.
I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.
I know that I am missing something basic but well, I can't figure it out.
Thanks
Bob
View 4 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
Nov 18, 2005
I have a table that stores part numbers and manufactuers. Somehow this table has become corrupt showing different manufacturers with the same part numbers.
I know this will take a bit of manual digging to fix, but I want to find a way to pull all rows that have the same part number that have different manufacturers, or just pull up any "duplicate" part numbers and I can determine what is right or wrong as far as the manufactuers and make those changes.
I have tried this, but it does not seem to want to work.
Code:
Select * from my_table
Where partnumber = (select partnumber from my_table) and compName <> (select compName from my_table)
I have tried other variations of the same, but nothing seems to want to show me just the items that have the same part numnbers and different manufacturers. I do not care if there are duplicates of the same part number/manufacturer entries, just if the part number is duplicated where the manufacturers are not the same.
These are the rows I want to edit and group by part number. I have almost a million rows of entries and this is not something I want to go through row by row. :-P
Any ideas?
View 2 Replies
View Related
May 1, 2015
I am trying to find a way where I can search for a column that is associated in all tables of the database. I have created a query but is not executing correctly.
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t8
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Status%'
ORDER BY schema_name, table_name;
View 3 Replies
View Related
Nov 2, 2015
I have a split string function that will take a comma delimited string and give back a table with all the values.I have a table that has a column with a comma delimited comma delimited list of states.
I can use a where clause to find one state in the table (such as all records that have CA in the states string).But need to find out how to find all the rows that have all or any of the states from a comma delimited parameter.Here is the schema
CREATE FUNCTION [dbo].[split] (@list nvarchar(MAX))
RETURNS @tbl TABLE (svar nvarchar(10) NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
[code]....
View 9 Replies
View Related
Jul 23, 2005
Could any suggest to me a good way to programmatically identify which SPsupdate a database column. I would like to create a cross reference for ourdatabase.
View 5 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
Dec 19, 2006
Example data
CA1000
CA10001
CA10002
CA10003
CA11597
CA11603
CA1001
CA998
CA999
As you can see, CA11603 is the largest number in this list.
When I try the follow sql code,
SELECT
MAX([MyCode])
FROM
[MyTable]
WHERE (SUBSTRING([MyCode], 1, 2) = 'CA')
The largest number comes back as CA997
When I try
MAX([MyCode])
FROM
[MyTable]
WHERE [MyCode] LIKE 'CA%'
The largest number comes back as CA997
SELECT
TOP 1 (SchoolMasterCode)
FROM
SchoolMaster
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA') ORDER BY Schoolmastercode
The largest comes back as CA10001
When I try....
SELECT
TOP 1 (SchoolMasterCode)
FROM
SchoolMaster
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA')
The largest comes back as CA1278
What am I doing wrong?
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
Feb 23, 1999
Does anyone have a TSQL utility (that they can share) that recursively searchs sysobjects for a matching input parameter string(for instance column name) for stored procedure object properties that returns the stored procedure name?
View 1 Replies
View Related
Feb 4, 2008
I've got a rowset (intermediate result in a large query) that's equivalent to this table:
create table T (
K bigint,
V bigint,
W tinyint,
primary key(K,W)
)
What I want is "for each K, find the V associated with the largest W". Or,
select K,V from T t1 where W = (select max(W) from T t2 where t2.K = t1.K)
What I'd like is a solution that does not involve a self-join on the table (which the sub-query effectively is). The rowset can be sorted by (K asc, W desc), in which case I'm looking for the first (K,V) pair for each unique value of K.
In the larger query, T could have 100's of thousands of rows, and as I mentioned, T is the result of another query on my base tables. I can express T as a CTE easily enough, but the resulting query plan (using the sub-select, as above) ends up performing the query that generated T repeatedly in order to implement the self join.
I can save T into a temp table - in fact, that's what I'm doing now - but I'd rather avoid using a temp table if possible. This query needs to be of the highest possible performance, so if there's a no-temp-table, no-self-join solution, I'm all ears!
I could imagine making a CLR UDF that does the filtering that I want, but I'm not convinced that it'd be a good choice - any insights on whether that's worth trying would be appreciated as well.
View 9 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
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
