Transact SQL :: Manipulating Full Name Field
Sep 22, 2015I have a column with Full Names (e.g. Jane Doe)...how can I transform it via t-sql for end resutl Doe, Jane?
View 8 Replies
ADVERTISEMENT
Transact SQL :: Server Text Field Not Returning Full Text
Apr 21, 2015I have a column in a table that has a type TEXT,when I pull the length of a row it returns 88222 but when I select from that column it dows not show all the text in the result set.
View 3 Replies View RelatedTransact SQL :: Return Field When A Field Contains Text From Another Field
Aug 25, 2015I'm new to SQL and I'm trying to write a statement to satisfy the following:
If [Field1] contains text from [Field2] then return [Field3] as [Field4].
I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.
Transact SQL :: NVARCHAR Field Getting Truncated After Increasing Field Length?
Aug 28, 2015I have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.
It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:
Inserted Value -> Result in table
123456-8 -> 123456-8 *Correct
1234567-9 -> 1234567- *Wrong
123456789 -> 123456789 *Correct
1234567#9 -> 1234567# *Wrong
1234567a9 -> 1234567a9 *Correct
Why is it that characters such as '-' and '#' are truncating the value, but only if the string is 9 chars long?
I'm currently using a direct t-sql insert statement in SQLExpress. The field is a simple NVARCHAR(9) field.
How Does Full Name Field Split To First Name And Last Name Field?
Oct 9, 2001I have a table with column person_name and I want to update that table and
insert the values of different columns FIRST_NAME and LAST_NAME.
Anybody can tell me the T-SQL code to do above problem?
Thank you very much!
Transact.log Is Full
Sep 2, 1999Hi!
I need help!
We have 98% transact log full.
After run
DUMP TRANSACTION WITH TRUNCATE ONLY it still doesn't purge the log.
How can we resolve problem.
Thank you,
sev
Full,DIFF And TRANSACT Backups
Jan 14, 2008 But i have one issue bothering me, it will be great if someone can help me out on this
1. I take full backup on Monday 8 pm which ends at 9.30 PM
2. Diff Backup starts at 10 PM every 2 hrs till Tuesday 7.59 PM
3. Transactional Log backup is happening every 10 min till tueday 7.59 PM
Now when i restore it in the same order i get error that Log in the set is too late to apply. I have recemmonded by team to stop log backup on the time they start full backups and take transactional log backup only after differentials are restored. Now is this a correct suggestion???
Also appendin the differential backup is a good idea or overwriting them is a better approach if Database is backedup every night and Differential occurs every 2 hrs.
Transact SQL :: VARCHAR (MAX) Not Showing Full Data
Nov 11, 2015I created a procedure called 'Longtextprocedure'. The length of this procedure is 650000 characters long. When I was giving the following query - select len(routine_definition) from INFORMATION_SCHEMA.routines where ROUTINE_NAME = 'Longextprocedure'; Length is showing as '4000' characters long. It is not showing the remaining part of the procedure, it is showing upto the 4000 characters of procedure code. But when you execute the procedure(exec 'Longtextprocedure') it is showing the exact result.
My question is i want to read all the 650000 characters long procedure code{select routine_definition from INFORMATION_SCHEMA.routines where ROUTINE_NAME = 'Longextprocedure'} to a variable which is varchar(max).
When i am trying to read the whole procedure code it is taking 4000 characters of data into the variable not the whole. Is there a way to read the huge procedure code into the variable?
Code:-
CREATE PROCEDURE FAKEPROCEDURE @procName VARCHAR(50) --@procName is 'Longtextprocedure'
AS
BEGIN
DECLARE @routineDefinition VARCHAR(MAX);
DECLARE @replaceToChar VARCHAR(MAX);
[Code] ....
When i am trying to execute the line EXEC(@dupliacteRouteDef), it is showing like there is no procedure defined. This is due to '@dupliacteRouteDef' variable is having 4000 characters of data not the whole data. Is there a way to read whole procedure data into the variable irrespective of the length of the string?
The Log File For Database 'tempdb' Is Full. Back Up The Transact
Oct 28, 2006Can anyone explaing briefly, why would this error appears???
Thank you
Transact SQL :: Failing To Change Owner Of A Full Text Catalog?
Oct 29, 2015I am having troubles to change an owner of a full text catalog. I am the owner of the database. I am doing this:
USE database;
ALTER AUTHORIZATION ON Fulltext Catalog::[ftrow_code_lookup_catalog] TO [dbo];
Catalog information:
Logical Name: ftrow_app_facility_catalog
FileGroup: ftfg_app_facility_catalog
But I get an error:
Cannot find the fulltext catalog 'ftrow_app_facility_catalog', because it does not exist or you do not have permission.
Manipulating Datetime
Dec 16, 1998Hi ppl
How do I subtract, say an integer from a datetime value. I tried to use the Convert function but wont work.
All I want to do in my SP is subtract an integer from getdate() and do a query based on the result
I get from that subtraction.
ex: getdate()-15 ?????????????????????????????????
Manipulating Strings
May 7, 2008Hi All
I've got a list of IDs seperated by commas and I want to get each indivdual ID and insert them into a table. Has anybody got any ideas how this can be done?
Thanks
Manipulating Dates In SQL
Jan 18, 2007I need to generate a date range, based on the current date (or an input date). I can get the correct dates using VB, but I haven't worked out the TSQL Syntax for them yet. Can anyone tell me the TSQL syntax for manipulating dates in the following way...?
Start Date... This is the first day of the month, one year ago... in VB I worked it out as...
dateadd("yyyy",-1,(cdate(cstr(Year(now))+"-"+cstr(Month(now))+"-01")))
End Date... This is the last day of the previous month... in VB I worked this one out as...
dateadd("d",-1,(cdate(cstr(Year(now))+"-"+cstr(Month(now))+"-01")))
eg. for today 18/01/2007 I would get a Start date of 01/01/2006 and an End date of 31/12/2006
Any help would be appreciated.
SQL Server 2008 :: Add Carriage Return To A Full Address Field
Apr 16, 2015I'm pulling individual address fields into my script, some of which are null.
I would like to add in a carriage return after ONLY those fields that are NOT NULL.
Current script is:
SELECT
ISNULL(SAD.HOUSENUMBER,'') + ISNULL(SAD.ADDRESS1,'') + ISNULL(SAD.ADDRESS2,'') + ISNULL(SAD.ADDRESS3,'') + ISNULL(SAD.CITY,'') + ISNULL(SAD.COUNTRY,'') + ISNULL(SAD.ZIP,'') as 'FULL_ADDRESS'
FROM
SFAADDRESS SAD
Manipulating Varchars As Datetime
Aug 10, 2007Hi all,
I am a little weak at SQL, so bear with me.
Can anyone give me an SP which uses "Between" to Display all Dates
BETWEEN a fromDate and toDate???
Two conditions
1.Both Dates are stored as varchar.
2. I should be able to get the dates even if years are different, say between 20th December 2006 and 3rd January 2007
3.Is there a way to extract the yearDiff?
Regards,
Naveen
Manipulating Data In Sqldatasource
Jan 16, 2008[I am new to asp.net]
Assume I have a databse for "products"; product id is a primary key.
Here is what I am trying to:
1. DetailsView for a product (eg id = 1110) (I got this far)
2. how can I list other products starting with 111x id
3. I also to show them on same page as 1110 as "parent" 111x as children
Thanks,
Manipulating Large Tables
Feb 15, 2008I'm in the midst of a long file conversion job. Today I found that one of the tables (converted from csv) to be 6.7 million records. My sql script which I use to reconfigure the weird original date format, into something the rest of the planet uses, times out due to the size.
Does anyone please know of a file utility to automagically split sql server 2005 tables for later re-combining once my scripts have successfully completed their task on the smaller tables?
Manipulating A SqlDatSource UpdateCommand In Code-behind
Jul 20, 2007We've got an employee database that I'm modifying to include two photos of each employee, a small thumbnail image and a full-size image. The HR department maintenance page contains a listbox of employee names, which, when clicked, populates a detailsview control.To get the images to display and be updatable, I've had to structure the following SqlDatasource and DetailsView:
1 <asp:DetailsView ID="dvEmp" runat="server"2 AutoGenerateRows="false"3 DataSourceID="dsEmpView"4 DataKeyNames="empID">5 <Fields>6 <asp:CommandField ShowEditButton="true" ShowCancelButton="true" ShowInsertButton="true" />7 <asp:BoundField HeaderText="Name (Last, First)" DataField="empname" />8 <asp:TemplateField HeaderText="Thumbnail photo">9 <ItemTemplate>10 <asp:Image ID="imgThumbnail" runat="server" ImageUrl='<%# formatThumbURL(DataBinder.Eval(Container.DataItem,"empID")) %>' />11 </ItemTemplate>12 <EditItemTemplate>13 <asp:Image ID="imgThumbHidden" runat="server" ImageUrl='<%# Bind("thumbURL") %>' Visible="false" />14 <asp:FileUpload ID="upldThumbnail" runat="server" />15 </EditItemTemplate>16 </asp:TemplateField>17 <asp:TemplateField HeaderText="Full Photo">18 <ItemTemplate>19 <asp:Image ID="imgPhoto" runat="server" ImageUrl='<%# formatImageURL(DataBinder.Eval(Container.DataItem,"empID")) %>' />20 </ItemTemplate>21 <EditItemTemplate>22 <asp:Image ID="imgPhotoHidden" runat="server" ImageUrl='<%# Bind("photoURL") %>' Visible="false" />23 <asp:FileUpload ID="upldPhoto" runat="server" />24 </EditItemTemplate>25 </asp:TemplateField>26 </Fields>27 </asp:DetailsView>28 29 <asp:SqlDataSource ID="dsEmpView"30 runat="server"31 ConnectionString="<%$ ConnectionStrings:eSignInConnectionString %>"32 OnInserting="dsEmpView_Inserting"33 OnUpdating="dsEmpView_Updating"34 SelectCommand="SELECT empID, empname, photoURL, thumbURL FROM employees where (empID = @empID)"35 InsertCommand="INSERT INTO employees (empname, photoURL, thumbURL) values(@empname, @photoURL, @thumbURL)"36 UpdateCommand="UPDATE employees SET empname=@empname, photoURL=@photoURL, thumbURL=@thumbURL WHERE (empID = @empID)">37 <SelectParameters>38 <asp:ControlParameter ControlID="lbxEmps" Name="empID" PropertyName="SelectedValue" Type="Int16" />39 </SelectParameters>40 </asp:SqlDataSource>41 42 -----43 44 Protected Sub dsEmpView_Updating(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)45 Dim bAbort As Boolean = False46 Dim bThumb(), bPhoto() As Byte47 If e.Command.Parameters("@ename").Value.trim = "" Then bAbort = True48 Dim imgT As FileUpload = CType(dvEmp.FindControl("upldThumbnail"), FileUpload)49 If imgT.HasFile Then50 Using reader As BinaryReader = New BinaryReader(imgT.PostedFile.InputStream)51 bThumb = reader.ReadBytes(imgT.PostedFile.ContentLength)52 e.Command.Parameters("@thumbURL").Value = bThumb53 End Using54 End If55 Dim imgP As FileUpload = CType(dvEmp.FindControl("upldPhoto"), FileUpload)56 If imgP.HasFile Then57 Using reader As BinaryReader = New BinaryReader(imgP.PostedFile.InputStream)58 bPhoto = reader.ReadBytes(imgP.PostedFile.ContentLength)59 e.Command.Parameters("@photoURL").Value = bPhoto60 End Using61 End If62 e.Cancel = bAbort63 End SubIf the user updates both images at the same time by populating their respective FileUpload boxes, everything works as advertized. But if the user only updates one image (or neither image), things break. If they upload, say, just the full-size photo during an update, then it gives the error "System.Data.SqlClient.SqlException: Operand type clash: nvarchar is incompatible with image".I think this error occurs because the update command is trying to set the parameter "thumbURL" without having any actual data to set. But since I really don't want this image updated with nothing, thereby erasingthe photo already in the database, I'd rather remove this parameter from the update string.So, let's remove the parameter that updates that image by adding the following code just after the "End Using" lines: Else
Dim p As SqlClient.SqlParameter = New SqlClient.SqlParameter("@thumbURL", SqlDbType.Image)
e.Command.Parameters.Remove(p)
(Similar code goes into the code block that handles the photo upload)Running the same update without an image in the thumb fileupload box, I now get this error: "System.ArgumentException: Attempted to remove an SqlParameter that is not contained by this SqlParameterCollection."Huh? It's not there? Okay, so lets work it from the other end: let's remove all references to the thumbURL and photoURL from the dsEmpView datasource. We'll make its UpdateCommand = "UPDATE employees SET empname=@empname WHERE (empID = @empID)", and put code in the dsEmpView_Updating sub that adds the correct parameter to the update command, but only if the fileupload box has something in it. Therefore: If imgT.HasFile Then
Using reader As BinaryReader = New BinaryReader(imgT.PostedFile.InputStream)
bThumb = reader.ReadBytes(imgT.PostedFile.ContentLength)
e.Command.Parameters.Add(New SqlClient.SqlParameter("@thumbURL", SqlDbType.Image, imgT.PostedFile.ContentLength))
e.Command.Parameters("@thumbURL").Value = bThumb
End Using
End If
(Similar code goes into the code block that handles the photo upload)But reversing the angle of attack only reverses the error. Uploading only the photo and not the thumb image results in: "System.Data.SqlClient.SqlException: The variable name '@photoURL' has already been declared. Variable names must be unique within a query batch or stored procedure."So now it's telling me the parameter IS there, even though I just removed it.ARRRGH!What am I doing wrong, and more importantly, how can I fix it?Thanks in advance.
Manipulating String To Return First Value Before Space
Dec 10, 2001I have data coming back like below
140 KB 8 KB 1450 KB
I would like to manipulate the string to pull out only the number value. There is always a space between the number and the "KB". Looked at replace but got stuck, any help appreciated.
Manipulating Individual Fields Of A View
Nov 13, 2007In my database I have a table for Users, with an int primary key, and a table for Connections, with a combined primary key consisting of two UserID foreign keys. (the smallest first)
At the point I am stuck I have one UserID, lets call it current_user, and a column returned by a select statement consisting of UserIDs. Some of these IDs will likely be smaller than current_user, and some will likely be larger.
What i need to do is construct a view of two columns, combining each of the UserIDs in the column I have with current_user, with the smallest UserID of each pair residing in the first column, and the largest in the second column.
The point of this is to then SELECT the connections identified by the UserID pairs.
I suspect I could accomplish this if I could set individual fields in the a view, but I seem to have missed (or forgotten) that lecture. Anybody want to clue me in?
Manipulating Multiple Count(*) Queries
Dec 15, 2006Hi,
I'm having problems manipulating the results from multiple count(*) queries.
I have a table 'RECOMMENDATIONS' holding a field called 'SCORE'. This field holds a number from 1 to 10 and has no null or zero figures.
I currently have the following three queries:
1) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)
2) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
3) SELECT COUNT(*) FROM RECOMMENDATIONS
I now need to combine these three queries so that i can divide and multiply the resulst: (query 2/query 1)*query 3
My initial idea was:
SELECT (COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)))* (SELECT COUNT(*) FROM RECOMMENDATIONS) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
... but this gives a result of "0". I then stripped out the multiplication section to see if the divide was working:
SELECT COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
... and again the result was "0"
Please could someone help me!!
Ian
Manipulating Table In Store Procedure
Mar 5, 2007Hi
I am new to SQL programming. This is what I am trying to do in store procedure.
Alter a table - adding two columns.
Then update those columns
Both the above action in same procedure .
When I execute them - it complains that the columns I am adding in the first part of store procedure , does not exists.
So the SP looks like this
create store procedure <name> as
alter table <name> ADD
column_1 nvarchar (256),
column_2 nvarchar (256);
update table
set
column_1 = <condition>
column_2 = <condition>
The SQL complains -
invalid column name column_1
invalid column name column_2
Can some one help please...
Manipulating The Result Set Of One Stored Procedure From Another....
Jul 23, 2005Hi,I have one stored procedure that calls another ( EXEC proc_abcd ). I wouldlike to return a result set (a temporary table I have created in theprocedure proc_abcd) to the calling procedure for further manipulation. Howcan I do this given that TABLE variables cannot be passed into, or returnedfrom, a stored procedure?Thanks,RobinExample: (if such a thing were possible):DECLARE @myTempTable1 TABLE ( ID INT NOT NULL )DECLARE @myTempTable2 TABLE ( ID INT NOT NULL )...../*Insert a test value into the first temporary table*/INSERT INTO @myTempTable1 VALUES ( 1234 )...../*Execute a stored procedure returning another temporary table ofvalues.*/EXEC proc_abcd @myTempTable2 OUTPUT......../*Insert the values from the second temporary table into the first.*/SELECT * INTO @myTempTable1 FROM @myTempTable2
View 1 Replies View RelatedManipulating GetDate() To Start At Midnight Rather Than Now
Apr 29, 2008Hi Folks,
I am trying to find the best way to use the getDate() function in SQL Server CE to return a date and time which starts at midnight rather than the value of now which getDate() returns.
When running getDate I get the value of 29/04/2008 10:48:33 returned, but I want to return 29/04/2008 00:00:00 instead.
The only way I can see to do this is like so:
Code Snippet
--SQL to get shifts for next 7 days.
select * from SHIFTS
where STARTDATE between
--Today at midnight 2008-04-29 00:00:00.000
(convert(datetime,
convert(nvarchar,(datepart(yyyy,getdate()))) + '/'
+
convert(nvarchar,(datepart(MM,getdate()))) + '/'
+
convert(nvarchar,(datepart(dd,getdate())))
))
and
--7 days from now at night 2008-05-05 00:00:00.000
(
convert( datetime,
convert(nvarchar,(datepart(yyyy,getdate()+6))) + '/'
+
convert(nvarchar,(datepart(MM,getdate()+6))) + '/'
+
convert(nvarchar,(datepart(dd,getdate()+6)))
))
Is there a better way to do this rather than this long winded method?
Thanks,
Morris
Manipulating A Text Box In Custom Code.
Nov 21, 2006Hi there,
A simple question I hope. I have got a textbox on a report and I'm trying to populate it by calling a custom assembly. I know I can reference it directly in the textbox (this works) but I am trying to do this from the code block. The following code didn't work:
Protected Overrides Sub OnInit()
ReportItems!textbox2.Value = POCCustomAssembly.CustAssembly.Hello()
End Sub
The TextBox is called textbox2 and the custom assembly simply returns a string.
I get an error message "The is an error on line 1 of custom code: [BC30469] Reference to a non-shared member requires an object reference".
What am I doing wrong?
Manipulating Large Ntext Data With ADO Recordset
Mar 20, 2000I am having a problem writing a large amount of ntext data to a field within an ADO recordset. I am using the append chunk method but it does not seem to work. The SQL 7 field will hold the data its only about 60K.
View 1 Replies View RelatedTransact SQL :: Query Fails To Retrieve Full Dataset Using Datetime After Successful Execution Of SSIS Package?
Jun 16, 2015I have an SSIS Package which Retreives Data using a SQL Query like below
select a.* from dbo.test a (nolock)
JOIN dbo.test1 b (nolock)
ON a.DetailsId = b.DetailsId
JOIN dbo.test c (nolock) on c.DetailsLineId = b.DetailsLineId
where convert(date,c.CpPlacedDate)>?
and convert(date,c.CpPlacedDate) < convert(date,GETDATE())
and c.PartnerCode in ('akakak07')
The CpPlacedDate DataType is Datetime. After the Successfull Execution of SSIS Package the final output results in destination is lesser than source.
The Maximum of cpplaceddate in the Destination for a particular date is '2015-06-13 23:46:08.923'
The Maximum of cpplaceddate in the Source for a particular date is '2015-06-13 23:59:14.873'
I am missing 16 records in between this time Gap.
Transact SQL :: Only Allow Certain Values In A Field
Nov 16, 2015I can not modify DDL of the table, so creating a trigger etc is not an option. This is my syntax that I am using, and each #Testing.abcd has different possible options.
For example, when #Testing.abcd = Test1 then #Data.field2
okay values are 'Yes', 'No', 'Maybe', but for #Testing.abcd = Test3 then #Data.field2 okay values are 'Follow-Up', 'Contact Requested'
How can I set-up "acceptable values" for one field, but different based off abcd in my syntax?
Declare abcd varchar(100), @sql varchar(1000)
Create Table #Testing (abcd varchar(100), efgh varchar(500)
Insert Into #Testing Values
('Test1', 'zzz'),
('Test2', 'xxx'),
[Code] ....
Transact SQL :: Way To Count Changes In Value Of A Field
Oct 13, 2015I want to avoid iterating through a table row by row and programmatically incrementing a counter,/I have define a view called Q2 with the following fields of interest:
Trial varchar(25)
BI int
Track int
CAT int
What I would like to do is partition by Trial, BI, and Track, and count the number of times CAT changes value. In Access, I would just iterate through a recordset inside a function, but I would like to see if there is a set based solution.Adding Sample Data and Output.
Trial BI Track CAT
A 5 3 1
B 5 3 2
[code]...
Transact SQL :: How To Get Value Of A Particular Field From XML Data
Nov 6, 2015We are running the following querry which insert data row from an XML source
INSERT INTO End_ReelProcessValue
(LineId
,ReelHist_id
,Variable_tagName
,ProcessMeasure
)
[Code] ....
From that querry, I need to store in a variable the Value of a particular XML entry in order to store it in an other table.The XML parsing is based on the following lines :
[Variable_Tagname] CHAR(50) '@Name',
[ProcessMeasure] NVARCHAR(254) '@Value'
What I need is that if the [Variable Tagname] issue from entry @Name is equal to the string "Quality", then I need to store its value to a variable.Then an other querry will use that variable to insert it to an other table.
How can I retrieve that particular record ?
Using A Regular Expression In MSSQL 2005 To Find Patterns Buried In A Field Full Of Misc Data
Feb 29, 2008Thanks in advanace for taking the time to read this post.
I am using MSSQL 2005 and have created a function that allows me to use regular expressions in my SQL queries.
My question is I have a pattern buried in a field of misc data that I need to pull out just that pattern and discard the rest of the data. Here is the Regular Expression I am using
select field1 from table1
where dbo.RegExMatch (field1,'[a-zA-Z]{4}[0-9]{6}[a-zA-Z]{2,4}')=1
This returns all values in the field that match the expression. What I want to do now is remove all data from the field on the left and right of the expression that does not match the expression. How would I accomplish this without reading through the 200k+ records and writing rules for every exception I run across?
so I could have Gar b/a ge 'THE GOOD DATA' m/or1 ba4d da....ta. All I want to do is return 'THE GOOD DATA'
Transact SQL :: List All Available Values Of A Field?
Aug 10, 2015I have [TableAccount.AccountType] field which can store these values:
Value
C
S
E
How do I list all available values for a field? Using Distinct can only list all entries in the db, but does not list all values available for the field.
Transact SQL :: How To Extract Date Value From XML Field Value
Oct 5, 2015I am having an XML field in a table "RMRECC" (field name: "Amount") and the value of the field is given below.
<X C="0" I="1">
<E D="1Y0M0W0D" P="0" A="0.05" />
<E D="1Y0M0W0D" P="0" A="0" />
<E D="1Y0M0W0D" P="0" A="0.03" />
</X>
I need to extract the date value "1Y0M0W0D" in MM/DD/YYYY format.how this can be handled in SQL query?