I imported data into a database and the first character in an ID Field starts with %. This is causing many problems for the application. Unfortunately, this field exists in 72 of 128 tables in the database. Is there a way to LTRIM every ID field where the first character is %? This is easy in 1 Table but how do I apply it to all 72 tables at once? Thanks for for your assistance
View 6 RepliesI Have a sql select statment and i need to trim white space off one of my columns .
How do I do that please help.
Could somebody please give me a syntax that I can use to trim spaces. I have used and I still have the spaces. :
UPDATE MAP SET ROAD = RTRIM (STREET)
UPDATE MAP SET ROAD = LTRIM (STREET)
We are trying to clean up spaces at road intersections. Thanks for your assistance.
I have some data that contains spaces both before and after the text string, and now I'm wondering what would be the best method to remove these blanks (sometimes there are no blanks, so I can't check with a specifik width)?
Is it possible to do something like:
set foo = ltrim(rtrim(foo))
or do I have to split it into 2 steps?
This trimming will be done in update & insert statements
// Pati
How do i remove carriage returns in SQL Server ? each of the lines have a carriage return as well as in front and back of the text.
Keith Waltin
Transport Ticketing Authority
03 9651 9066
I've tried the
update test.dbo.test
set bodytext1 = ltrim(rtrim(bodytext1))
but the whitespace/carriage returns still exists in the back and front of the text ? Anyone got any ideas ?
Actually, I don't know what is the meaning and difference of "N" in thefunction.Thanks.
View 1 Replies View Related
can anyone explain me what happens when we write the above for a col.
I k now it remove spaces but can anyone explain with exmpls
thanks
Hello All,
I am trying to ltrim a portion of multiple fields in a grouping. I am able to do it for one of them, but unfortunately there are several I have to do it for. If I use the following expression, it works for that one.
Code Snippet
=iif(Fields!BankNumber.Value="083" and Fields!TestName.Value="Inquiry Menu - Bank 083",LTRIM("Inquiry Menu"),Fields!TestName.Value)
However, if I try and do it for more than one it errors out. For example...
Code Snippet
=iif(Fields!BankNumber.Value="083" and Fields!TestName.Value="Inquiry Menu - Bank 083",LTRIM("Inquiry Menu"),Fields!TestName.Value)
OR iif(Fields!BankNumber.Value="083" and Fields!TestName.Value="Search Menu - Bank 083",LTRIM("Search Menu"),Fields!TestName.Value)
OR iif(Fields!BankNumber.Value="083" and Fields!TestName.Value="SEAX - Bank 083",LTRIM("SEAX"),Fields!TestName.Value)
Is there another way to arrange this so I can LTRIM each field group seperately?
Thanks,
Clint
Hi i have a select statement as
select empnum, len(empnum), ltrim(rtrim(empnum)), len(ltrim(rtrim(empnum))) from employee
When i execute this stament i get the following
1234 61234 6
4321 84321 8
1111 61111 6
2222 62222 6
How does this happens. Why ltrim and rtrim is not working here.
You all have been so much help, but I've discovered yet another problem. I'm trying to clean up my table using the following command:
UPDATE dbo.TableName
SET First_Name = LTrim(RTrim(First_Name))But it does not seem to have any effect. Thoughts? Thanks!
How do I "ltrim" an enire colum?
Thanks,
I am trying to follow an SSIS tutorial. It is doing a transformation on mainframe data.
It has the statement LTRIM(State) == '' in the Condititonal split editor.
The data type is DT_STR.
Nothing works (dbl quote,brackets, ect)
I have also tried to change the type to DT_WSTR. According to docs ltrim only works with Unicode.
Can someone please tell me how to detect an empty string.
Thanks for any help
walter
I need some help.I am trying to write a query which does the followingSELECT * from table1 where field1=(SELECT distinct field1 FROM table1WHERE field2='2005' or field2='2010')I need all the values from table1 which match any value from field 1from the subquery.Any help is appreciated.thanks
View 4 Replies View RelatedHow do I convert Oracle's LTRIM(char, set) to SQL Server?
Thanks,
Jake
I'm not sure about why I'm not able to remove spaces even after trimming them. Below is the result of query I'm usning.
select distinct LTRIM(RTRIM(Promotion_Code)) Promotion_Code
--, count(Promotion_code)
from dbo.Marketing_Promotion_Tb
where Promotion_code like '%1BTPIZZA%'
Result :
Promotion_Code  Length
1BTPIZZAÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 10
1BTPIZZAÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 8
Hi
I sent a long string of ID from front end to my stored procedure...till now I was using varchar(8000)...but if the string crossess that limit it is breaking.
If I try to use text datatype..It doesn't support rtrim, stuff functions etc...
So could any one suggest me a best way to save a long string without any restriction of size...
My front end is C#.Net and Back End is SQL SERVER 2000
Thanks in advance
i have two tables
Table 1 - AccountRent
Fields are
RentAmount RentYear
100 2004
200 2004
300 2004
400 2005
500 2006
Table 2 - Insurance
Fields are
InsuranceAmount ReceivedYear
100 2002
200 2002
300 2003
400 2003
500 2005
600 2005
I Need to sum these two tables 'Amount' based on the Year The Result for
IncomeAmount Year
300 2002
700 2003
600 2004
1500 2005
500 2006
Regards,
Prabu R
Do you have any sql statement for find the field difference between two tables?.
View 2 Replies View RelatedThis is my curren code for updaing ESN number. But this is incorrect.
<asp:SqlDataSource ID="ESNTrackingDataSource"
UpdateCommand="UPDATE [ESNTracking] SET [EsnNumber] = @EsnNumber WHERE [EsnTrackingId] = @EsnTrackingId"
OnInit="ESNTrackingDataSource_Init"
OnUpdating="ESNTrackingDataSource_Updating"
OnUpdated="ESNTrackingDataSource_Updated"
runat="server">
<UpdateParameters>
<asp:Parameter Type="String" Name="EsnNumber"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
this is the Select statment I am trying to use so that I can update the Asset in Assets table and the ESN number in the ESN table. But using ESNId and AssetId.
This is my query that returns the ones are not assigned to and
Asset
SELECT DISTINCT EsnId, EsnNumber
FROM dbo.ESNTracking
WHERE (EsnId NOT IN (SELECT EsnId FROM dbo.EsnAsset))
I am trying to join two tables together using a particular field. The code to reproduce is:
declare @chris as table
(
chrisid int identity (1,1) primary key,
name varchar(100),
SrvStopID int
)
[Code]...
My result is:
chrisidname SrvStopIDchrisidname SrvStopID
1Neal 1 1John 1
1Neal 1 2Jacob 1
1Neal 1 3Jinglehiemer 1
1Neal 1 4Smith 1
2SmithSon 1 1John 1
2SmithSon 1 2Jacob 1
2SmithSon 1 3Jinglehiemer 1
2SmithSon 1 4Smith 1
and my result should be: Just the results in @Neal that match SrvStopID in @Chris
I have a database with 10 tables
I have a field named CustID .... I want to find out how many tables have this field CustID
Now one way was check up the design of every table & see whether its there or not (the field)
is there any query through which we can get this info Please help
I have two tables I need to join but there are 2 fields which theycould be joined on.Using the example Tablles, TableA and TableB below;TableAID1 ID2 Qty1 Null 42 A 5Null B 6TableBID1 ID2 QtyNull A 63 B 64 Null 7Null C 8I want to create TableC which will look like this;ID1 ID2 TableA.Qty Tableb>Qty1 Null 4 Null2 A 5 63 B 6 64 Null Null 7Null C Null 8Any ideas?Regards,Ciarán
View 2 Replies View RelatedHi,I have a 2 tables called 1.tblRisk which consists of Ref(pk), subject, status, staff & Dept(fk)2.tblDept which has Ref(Pk) & DepartmentHow do i get it to populate Department, when tblRisk Ref's Dept matches the Ref in tblDept i am using SQL Server 2000best regards
View 1 Replies View RelatedI have two tables tbl1 and tbl2, which I do a full outer join between tbl1 and tbl2 on recordId field. The recordId field is not a key in either of the tables.
If there is one row each for a recordId 123 in both tables, the select query would return one combined row.
If tble1 had two rows for recordId 123, and tbl2 had one row for the same, it would return to rows repeating the data in tbl2.
If tbl2 had two rows and bl1 had one row, it would return two rows in output repeating the data in tbl1.
Is the above correct? Would the result be different if it was an inner join instead of full outer join?
Is it ever possible that one of the two records with recordId 123 will be dropped from the result?
I'm managing an amature online university and I've been charged with creating a deans list. I have a table for exam results for each course.. currently totaling 5. I have an employeeID column and a total_points column in each table. Sooooo I need to join all the tables and get an average for total_points where the employeeID matches across tables. I have no idea how to write this select.. any help?
View 1 Replies View RelatedIs there a way ,(if so what is the syntax?), to set up a DTS package
that loads a table that has an identity column. I am trying to load the data from another table, (leaving the identity field unmapped), and de-selecting the "enable identity insert" from the advanced tab of the Data Transformation Properties window. I keep getting errors due to the table not allowing null values. I tried using the set_identity command, but this still did not work.
Any help would be appreciated.
TB
This is feeling very hard for me, but is surely very easy for many of you.
I have 2 Tables. "Events" and "Meals". Both have a columns named "EventDate" and "EventTime". I need to be able to compile a list of both and sort by event date and time. For example, a Meal @ 5:30 would place itself between a 4:00 Event, and a 6:30 Event.
PLEASE HELP ME!!!
Pending deadline doom :(
Thanks,
kaskenasy@publishingconcepts.com
I know there is some kind of rule against the following SQL statement, but I was wondering what to do to get around this problem (some kind of grouping). Sorry for the stupid question.
SELECT * FROM Table1, Table2 WHERE Table1.ID IS NOT NULL AND Table2.ID IS NOT NULL
Basically I want to select all records from the two tables (they have the same fields, but are just different specialties) and then output them, but there is nothing in common between the two to reference one another, and it ends up in some kind of loop. Thanks. for the help.
I have a table activity which show the activities of the site. An activity could be related with no other entity or it could be related with an account, or with a contact or with an opportunity. An opportunity and a contact could be also related with an account.
My problem is that I need to show the activity and the related account (if there is any) but because the relationship with an account could be done by different ways, I dont know how to do it without join the account table several times.
This is my current query:
SELECT AC.IdActividad, CU.idcuenta
FROM Actividades AC LEFT JOIN Tareas TA ON TA.IdActividad = C.IdActividad
LEFT JOIN ActividadesXCuenta AXC ON AC.IdActividad = AXC.IdActividad LEFT JOIN dbo.Cuentas CU ON axc.IdCuenta = CU.IdCuenta
LEFT JOIN dbo.ActividadesXOportunidad axo ON AC.IdActividad = axo.IdActividad LEFT JOIN dbo.Oportunidades o ON axo.IdOportunidad = o.IdOportunidad LEFT JOIN dbo.Cuentas CU1 ON o.IdCuenta = CU.IdCuenta
LEFT JOIN dbo.ActividadesXContacto axco ON AC.IdActividad = axco.IdActividad LEFT JOIN dbo.Contactos con ON axco.IdContacto = con.IdContacto LEFT JOIN dbo.Cuentas CU2 ON o.IdCuenta = CU.IdCuenta
As you see, I have the table Cuenta joined 3 times. That means that I need to put the ActivityID field 3 times but based on how the report was developed, I could not do that.
I have two tables: TestA and TestB. Both tables have 3 fields: ID,Name, and RunDate. I need to create a query which will join the twotables first on Name but then I need to match up the RunDates eventhough the RunDates won't be the same.CREATE TABLE TestA (ID INT IDENTITY, Name VARCHAR(255), RunDateDATETIME)CREATE TABLE TestB (ID INT IDENTITY, Name VARCHAR(255), RunDateDATETIME)INSERT INTO TestA VALUES ('Account 1', '9/1/2004 12:00PM')INSERT INTO TestB VALUES ('Account 1', '9/1/2004 12:15PM')INSERT INTO TestA VALUES ('Account 1', '9/2/2004 1:00PM')INSERT INTO TestB VALUES ('Account 1', '9/2/2004 1:15PM')INSERT INTO TestA VALUES ('Account 1', '9/3/2004 3:00PM')INSERT INTO TestA VALUES ('Account 2', '9/5/2004 4:00PM')INSERT INTO TestB VALUES ('Account 2', '9/5/2004 4:15PM')Here's a common scenario:User updates TestA data for Account 1 on 9/1/2004 at 12:00pm. Thenthe user updates TestB data for Account 1, 15 minutes later. I wantthese two records to match. The user must always update TestA databefore they update TestB data. Therefore, there might be more rows inTestA then in TestBHere's what the results should look like for the above data.Name TestA Date TestB Date---- ---------- ----------Account 1 9/1/2004 12:00pm 9/1/2004 12:15PMAccount 1 9/2/2004 1:00pm 9/2/2004 1:15PMAccount 1 9/3/2004 3:00pm (NULL)Account 2 9/5/2004 4:00pm 9/5/2004 4:15PMAny help would be much appreciated!!!!
View 2 Replies View RelatedI want to group by a fields value in a table. I have a purchase table where contracts and orders are stored in together. Now what I need is, I have to group only by the rows that contain an identical contract_no and order_no as those records represent the actual contract. the other rows contain order data-sets which need to be subtracted from the contracts values.
datasource:
contract_no order_no name amount
12 12 contract1 1,000,000
12 215 order215 50,000
12 460 order460 75,000
280 280 contract2 500,000
280 340 order340 10,000
280 410 order410 9,000
I want to display each order that took place via drilldown/hide function in the table, and calculate the amount from the contract that is left.
desired result report:
12 contract1 1,000,000
order 215 50,000
order 460 75,000
amount left 875,000
Here is my situation. I am building a report that has three different tables each with their own dataset. Example: Opportunities, Leads, Activities. All three of these datasets/tables have a common field - SalesID. I would like the report to show the first SalesID, then all Opportunities for that SalesID in the Opportunities table, followed by all Leads for that SalesID in the Leads table, followed by all Activities for that SalesID in the Activities table, and then rollover to the next SalesID and repeat that for all SalesIDs. Any suggestions on how I could achieve this? Thanks in advance for all help!!!
View 6 Replies View RelatedHello-
I have a sql query that I am using to populate a datagrid. The problem is one of the tables is a month table. and the other tables are full of data. So there is no common column name to match using a inner join "on".
How do i do this?