Help With SQL Statement To Find First And Last Populated Field In String Of Fields
Jun 21, 2006
Hello all,
I'm at a loss on how to do this. We're using MS SQL 2000 Server and I
have a list of fields I need to find the first and last entry for.
Here's an example of the table:
Number - VarChar(10)
Jan - Int
Feb - Int
Mar - Int
Apr - Int
May - Int
June - Int
ANd it'll look something like this:
NumberJanFebMarAprMayJun
1232200190192201203205
4432433322 456
5423754694665
I'm not sure if this'll copy over correctly, but I have gaps in the
data so I can't just say Jun-Jan, but I need tofind the first field
with data and last field with data, then find the difference of these.
Suggestions? Is there a loop or something I can do in TSQL that'll do
this? I'd like to do this in Query Analyzer since it's just a one-time
report. Thanks --
I'm trying to find a specific string (a name) and replace it with another inside of a VARCHAR(7000) field. Unfortunately, there are names like Ted and Ken that I'm trying to replace. I would like to leave words like Broken, admitted, etc... intact.
UPDATEtbl SETBody = LEFT(REPLACE(tbl.Body, pm.OldFirstName, p.FirstName), 7000) FROM Table tbl JOIN Person p ON p.PersonID = tbl.PersonID JOIN PersonMap pm ON pm.PersonID = p.PersonID AND LEN(pm.OldFirstName) > 2 WHEREtbl.Body LIKE '%[^a-z]'+pm.OldFirstName+'[., ]%
'The problem I'm running into is that the '[, ]%' in the LIKE excludes any record that ends with the FirstName because it is requiring either a space, comma or period after the name. Is there some way to add an empty string to the list of acceptable characters as that would cover any scenario in the data? I would prefer not to add all characters except space, comma and period, but I guess I could do that.
How can I change a field type whilst it is populated?
I have tried :
insert new_table select * from old_table
but I get :
Disallowed implicit conversion from datatype 'text' to datatype 'varchar' Table: 'davy.dbo.new_table', Column: 'de_area' Use the CONVERT function to run this query.
My table formats are as follows :
TABLE dbo.new_table ( de_pk int NOT NULL , de_name char (25) NOT NULL , de_area char(255) NULL )
TABLE dbo.new_table ( de_pk int NOT NULL , de_name char (25) NOT NULL , de_area text(16) NULL )
Hi, I have a script task in SSIS which dynamically generates a string for a file name to be used as flat file source. I execute the task and it executed with success; but when I checked the result of the variable TotFileName from the Expression builder window for the flat file connection manager it was not populated with a file name like \MyServerMyDriveMyFolder200706daily.txt. So something might still be missing from the script below. Or is the way I do it correct? Can someone help with this? Thanks a lot!!
I have created package level variables ImportFolder (value like: \MyServerMyDriveMyFolder ) and TotFileName (value field empty) and make ImportFolder a ReadOnlyVariable and TotFileName a ReadWriteVariable. Then I use expression to set the property for flat file connection manager to use the TotFileName variable.
(Basically the idea is: if now is July 2007 then the filename should be 200706daily.txt; if now is Jan 2008 then the filename should be 200712daily.txt)
-----code------
' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task.
Imports System Imports System.IO Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim TotFileName As String Dim TrueFileName As String Dim sYear As String Dim sMonth As String Dim sDate As String
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
I know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?
I have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like Create proc create procedure create proc create view create function
I need its result as:
Alter Procedure Alter Procedure Alter Procedure Alter View Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
I have table 'A' containing Company information including the company zip code. I have table 'B' which is a table of ZipCodes. How can I get a result set of all the Zip Codes that exist in 'A' but not in 'B'? Thank you
Serial Count 001 2 the count is 2 because Serial 001 has an MSDSID of 20 and 22 002 1 the count is 1 because Serial 002 only has MSDSID 21 003 2 the count is 2 because Serial 003 has an MSDSID of 21 and 22 004 1 the count is 1 because Serial 002 only has MSDSID 23
It would be even better if the results just showed where the count is greater than 1.
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are:
Field02
----------------------
22810 8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760 22810
Is there any good suggestion? Thanks in advance for any help, Aldo.
I am trying to find books which have the same title and publisher name as at least two other books and need to also show the book ref (ISBN number). I have the below script so far:
SELECT isbn, title, publishername FROM book WHERE title in (SELECT title FROM book GROUP BY title HAVING count(title)>2 or count(publishername)>2) order by title;
This is a snap shot of the output:
ISBN Title Publishername 0-1311804-3-6 C Prentice Hall * 0-0788132-1-2 C OSBORNE MCGRAW-HILL * 0-0788153-8-X C OSBORNE MCGRAW-HILL * 0-9435183-3-4 C Database Development MIS * 1-5582806-2-6 C Database Development MIS
[Code] ....
What I should be seeing is only the ones I have put an * next to. What am I missing from the scrip?
The formula for the above calculated fields are as below:
Opening Balance = carried forward balance from Year 2005 Debit = All positive amount Credit = All negative amount Net Change = Total Credit - Total Debit in Period 01 Balance = Total of Net Change + Opening Bal
Guys, hope someone out there can help me with the sql command for the above report?
SELECT DENSE_RANK() OVER (ORDER BY clroot.Ctgry1.Descr ASC) AS Row1, Row_Number() OVER (partition BY clroot.Ctgry1.Descr ORDER BY clroot.Ctgry1.Descr, T1.Descr ASC) AS Row2, left(t1.ID,4)+right(t1.levelid,4) AS ERPID,T1.ID AS Ctgry1ID, clroot.Ctgry1.ID AS ParentID, T1.LevelID, clroot.Ctgry1.Descr AS Category, T1.Descr AS SubCategory,
The formula for the above calculated fields are as below:
Opening Balance = carried forward balance from Year 2005 Debit = All positive amount Credit = All negative amount Net Change = Total Credit - Total Debit in Period 01 Balance = Total of Net Change + Opening Bal
Guys, hope someone out there can help me with the sql command for the above report?
I am setting up a database that will receive a lot of data from twoseparate telephone centers, the log table will in a short time haveover 1 million lines, and I was wondering if I should use 1 identifyfield or two:case 1:[Id] [int] IDENTITY (1, 1) NOT NULL[ServerId] [int] NOT NULLcase 2:[Id] [varchar(20)] IDENTITY NOT NULLWhere in case 1 I would just use a combination of Id and ServerId toidentify the line, where in case 2 I would have the Id field a varcharthat would look something like A-000001, A-000002 for server 1 andB-000001, B-000002 for server 2Which solution will be faster when searching for a record when thewill have over 1 million lines?
Link ( GroupID int , MemberID int ) Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID, then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
I have a table with a startdatetime and an enddatetime column such as:StartDateTime EndDateTime what I want to see returnedis:01/29/2004 10:30AM 01/29/2004 1:30PM "1/29/2004 10:30AM - 1:30PM"01/29/2004 10:30AM 01/30/2004 1:30PM "1/29/2004 10:30AM - 1/30/20041:30PM"01/29/2004 10:30AM 01/30/2004 10:30AM "1/29/2004 10:30AM - 1/30/200410:30AM"Maybe someone has accomplished this aready in a stored procedure andhas an example of how to do it?lq
greetings!! the below trigger works fine in SQL 2000 and doesn't takeup much resources, it's a very simple solution to trim text fields atinput in SQL. I know a lot of folks will say to create input masks atthe UI level and that SQL is a restricted back-end DB, but to be quitehonest I don't trust those UI guys and as a DBA I will have to cleanthe mess when they forget to validate. Hope it can be usefull to anyof you too. ;)----------------------------------------------------------------------CREATE TRIGGER [TRIMMER_TGR] ON [dbo].[MyTable]AFTER INSERT, UPDATEASIF UPDATE (MyStringField)DECLARE @TRIMMEDFIELD NVARCHAR(50)DECLARE @MYID INTSELECT @TRIMMEDFIELD = MyStringField from InsertedSELECT @MYID = ID from InsertedBEGINUPDATE tblDocket SET MyStringField = RTRIM(LTRIM(@TRIMMEDFIELD))WHERE ID = @MYIDEND
I'm struggling with this. I'd like to perform a joined query from two or more tables and take the same field from several rows of one table into seperate fields of a single row in a new table.
