Using A Convert And Trim Statement Combined
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Using A Convert And Trim Statement Combined
I need t do some date converts on some varchar fields which might have trailing spaces. I tried this below but still select df_ppd, df_xray_date from patient_ where convert(datetime,rtrim(df_ppd),101) > '06/20/2000' received a conversion error message Server: Msg 242, Level 16, State 3, Line 4 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Any ideas?
View Replies !
View Related
Need Help With SQL Statement (combined SELECT And WHERE At Same Time)
I am building a file repository page. For data source I use SQLDataSource and Repeater to display the categories. I am trying to achieve the result of displaying the following data: List of all "Main Categories" and all "Sub Categories" under each main category name. Also, "Total Number of files" and "Category Description" under each "Main Category". Sample: Human Resources All documents related to new hire process. New Hire Request forms, New Hire forms, Termination Forms Total Files: 25 Programs & Utilities Windows utilities and other tools to keep you safe on the net. Antivirus, Spyware Removal Tools, Other programs Total Files: 37 My SQL2005 DB has 2 tables. 1) FileCategories. Fields(CategoryID, ParentCategoryID, CategoryName, CategoryDescription) 2) Files. (CategoryID, ParentCAtegoryID, FileTitle) My "Main Categories" are the ones that has a NULL in ParentCategoryID field. Because they are the Parents. My each "Sub Category" has it's own ID as well as parentCategoryID. This insures that each Sub Category corresponding to it's parent only. Sample: Human Resources. CategoryID=4, ParentCategoryID=NULL New Hire Request forms. CategoryID=13, Parent CategoryID=4 New Hire Forms. CategoryID=17, Parent CategoryID=4 Termination Forms. CategoryID=22, Parent CategoryID=4 Programs & Utilities. CategoryID=6, ParentCategoryID=NULL Antivirus. CategoryID=8, Parent CategoryID=6 SpyWare Removal Tools. CategoryID=26, Parent CategoryID=6 Other Programs. CategoryID=31, Parent CategoryID=6 When I am creating a sql Select statement first I need to choose All "CategoryIDs" where "Parent category" is NULL. Then All "CategoryIDs" where "Parent categories" are not NULL and corresponding to Main Categories. Is it something accomplishable with SQLdataSource or I have to use ObjectDataSource or something else. I'll be on the site all day today (10/29/07) and tomorrow (10/30/07) checking for replies. Thanks in advance and let me know if you have any questions.
View Replies !
View Related
SELECT Statement To Display Combined Fields
Dear All I am trying to populate an OledbDatareader for binding to a ASP datagrid. For this I use select statement to display combined fields in a datagrid cell. Eg. Select (Field1+ '<br/>' + Field2 + '<br/>' + Field 3) As Address .. and so on. But the problem is if any of the three field is null the combined field 'Address' returns as Null. Please help me to overcome this problem. Regards kalanad ( beginner)
View Replies !
View Related
Convert Text To Float In SQL Statement
Hi, Can you guys help me out? I m trying to sum up some varchar-typed field. I need to convert it to float before doing the summing up so I m using "Cast". I do get the answer but its not the correct figure. My SQL statement is as follow: SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM TableName Please help.
View Replies !
View Related
Convert Fields In Select Statement
The data I have has a 1 in for each Yes answer and a 2 for each no answer. I just want the select statement to show the word yes when there's a 1 and the word no when there's a 2. I don't need to update or change the database. Could anyone lead me in the right direction here? Thanks
View Replies !
View Related
How To Convert SELECT Into UPDATE SQL Statement
Hello! I am trying to write an update t-SQL statement from the following select statement: SELECT EduNextContacts.ssn FROM EduNextContacts Left JOIN EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code GROUP BY EduNextContacts.ssn HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) + SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2) I tried many versions of writing my update statement with no luck. My most recent version is as follows: UPDATE EduNextContacts SET EduNextContacts.overLMLimit = 'Y' FROM EduNextContacts Left JOIN EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code WHERE EduNextContacts.ssn = (SELECT DISTINCT EduContactsAuditChanges.ssn FROM EduNextContacts Left JOIN EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code GROUP BY EduContactsAuditChanges.ssn HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) + SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2)) And gives the following error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Can anyone help shed some light on how to make an update statement from my SELECT query above? Thanks in advance, Harry
View Replies !
View Related
Convert Ms Access Sql Staement To Oracle Sql Statement
Hi,I currently have a ms access update query that runsperfectly well and quicly in access however I now need to add this queryto convert this qeryu to oracles equivelant sql syntax and add it to the endof an oracle sql script.Unfortunately Im not having much success although i seem to be able toconvert it to a working oracle sql. it takes hours to run the statement inoracle where as in access it runs in secondsany help is appreciated.Ms Access sql :UPDATE (PRO_STY_TPRICES INNER JOIN PRO_STYLE_COLOURS ON PRO_STY_TPRICES.STY_ID = PRO_STYLE_COLOURS.STY_ID) INNER JOIN PRO_TST_RV3X_RPT_WRK ON(PRO_STYLE_COLOURS.SEASON = PRO_TST_RV3X_RPT_WRK.SEASON) AND(PRO_STYLE_COLOURS.STY_NUM = PRO_TST_RV3X_RPT_WRK.STY_NUM) AND(PRO_STYLE_COLOURS.STY_QUAL = PRO_TST_RV3X_RPT_WRK.STY_QUAL) AND(PRO_STYLE_COLOURS.BF_MAT_CHAR_VAL = PRO_TST_RV3X_RPT_WRK.BF_MAT_CHAR_VAL)SET PRO_TST_RV3X_RPT_WRK.MKD_DATE = pro_sty_tprices.new_active_date,PRO_TST_RV3X_RPT_WRK.MKD_PRICE = pro_sty_tprices.new_tpriceWHERE (((PRO_STY_TPRICES.NEW_ACTIVE_DATE) Is Not Null));Oracle SQL :update pro.tst_rv3x_rpt_wrk xset(x.mkd_date, x.mkd_price) =(Select a.new_active_date, a.new_tpricefrom pro.sty_tprices a, pro.style_colours bwhere a.sty_id=b.sty_idand b.bf_mat_char_val = x.bf_mat_char_valand b.season = x.seasonand b.sty_num = x.sty_numand b.sty_qual = x.sty_qualand a.new_active_date is not null)
View Replies !
View Related
Combined Result...
hi! can anybody please help me...what would be my query string if i want to combine 3 column into one column? example. I have 3 columns in my customer table namely street,City,postal_code and i want to query that 3 column as address having it combined. thanks in advance.
View Replies !
View Related
Can These Tables Be Combined?
I have currently created a design which uses three main tables for storing information related to financial actions. The two tables I wish to combine are described below. There is a third table after the OrderTransactions table which contains information about each step of a transaction. This means that anytime I have to write a query to get information down at the transaction activity level (very frequently), I will have to always perform two joins. Would it be acceptable in this scenario to combine the Orders and OrderTransactions tables, and place a ParentOrderID field in there? A transaction would either have no parent, or would have to belong to a parent that does not have a parent. This means that the information in the Orders table will be duplicated for each transaction. The data in the Orders table is more or less static after its initial insert. The data there is never updated, no matter which approach is used. Either approach will work, I'm just looking to see what some of the people more knowledgeable than me think of the situation. Orders: Contains the core order information pertaining to all transactions CREATE TABLE [Orders] ( [OrderID] [int] NOT NULL , [MerchantID] [int] NOT NULL , [CustomerID] [int] NOT NULL , [PaymentMethodID] [int] NOT NULL , [IsTestOrder] [bit] NOT NULL , CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [OrderID] ) ON [PRIMARY] ) Transactions: Each order may have one or more transactions. All of the information in the Orders table is pertinent to a given transaction. CREATE TABLE [OrderTransactions] ( [OrderID] [int] NOT NULL , [TransactionID] [int] NOT NULL , [TransactionTypeID] [int] NOT NULL , [CustomerIPAddress] [bigint] NOT NULL , [Description] [nvarchar] (250) NOT NULL , CONSTRAINT [PK_OrderTransactions] PRIMARY KEY CLUSTERED ( [OrderID], [SequenceID] ) ON [PRIMARY] , CONSTRAINT [FK_OrderTransactions_Orders] FOREIGN KEY ( [OrderID] ) REFERENCES [Orders] ( [OrderID] ) )
View Replies !
View Related
Combined Values
Hi everybody, although I don't know much about complicated SQL stored procedures, I have to adjust the next procedure: CREATE PROCEDURE dbo.downloadSelectie( @mailingID int ) AS SELECT dbo.ContactHistorie2.PersoonID , dbo.Personen.KoppelID, dbo.Personen.Achternaam, dbo.Personen.Voorletters , dbo.Personen.Voorvoegsels, dbo.Personen.Roepnaam, dbo.Personen.VoorvoegselsPartner , dbo.Personen.AchternaamPartner, dbo.Personen.AchternaamPartnerGebruik , dbo.Personen.Geboortedatum, dbo.Personen.Geslacht, dbo.Personen.NationaliteitsCode , dbo.Personen.PartnerID, dbo.Personen.overleden, dbo.Personen.Password , isis_interface.dbo.ID_NATIONALITEITEN.OMSCHRIJVING AS Nationaliteit , dbo.Adressen.StraatNaam, dbo.Adressen.HuisNummer, dbo.Adressen.HuisnummerToevoegsel , dbo.Adressen.Postcode, dbo.Adressen.Woonplaats, dbo.Adressen.LandCode , dbo.Adressen.EMailAccount, dbo.Adressen.TelefoonNummer1, dbo.Adressen.TelefoonNummer2 , dbo.Adressen.FaxNummer, dbo.Adressen.ActueelAdres, dbo.Adressen.MutatieDatum , dbo.Adressen.GemuteerdDoor, dbo.Adressen.GemuteerdDoorIP , dbo.Landen.OMSCHRIJVINGUK AS adres_land , dbo.Landen.OMSCHRIJVING AS adres_land_nl , dbo.Landen.PC_code AS adres_land_pc_code , dbo.Landen.GebruikPC_code as adres_land_gebruikPC_code , ISNULL(dbo.v_PersoonOpleidingenUniek.opleidingen, '[onbekend]') AS faculteitJaar , ISNULL(dbo.v_PersoonOpleidingenUniek.opleidingenSec, '[onbekend]') AS Faculteit , dbo.[Curriculum Vitae].Omschrijving AS werk_omschrijving , dbo.[Curriculum Vitae].Toelichting AS werk_toelichting , dbo.[Curriculum Vitae].Functie AS werk_functie , dbo.[Curriculum Vitae].NaamBedrijf AS werk_organisatie , dbo.[Curriculum Vitae].Afdeling AS werk_afdeling , dbo.[Curriculum Vitae].Adres AS werk_adres , dbo.[Curriculum Vitae].Postcode AS werk_postcode , dbo.[Curriculum Vitae].Woonplaats AS werk_woonplaats , dbo.[Curriculum Vitae].Landcode AS werk_landcode , Landen_1.OMSCHRIJVINGUK AS werk_land , Landen_1.PC_code AS werk_land_pc_code , Landen_1.GebruikPC_code as werk_land_gebruikPC_code , dbo.[Curriculum Vitae].Telefoonnummer AS werk_telefoon , dbo.[Curriculum Vitae].[E-MailAdres] AS werk_email , dbo.Contacten.CommunicatieSchriftelijk, dbo.Contacten.CommunicatieEmail, dbo.Contacten.CommunicatieTelefonisch FROM dbo.Landen Landen_1 RIGHT OUTER JOIN dbo.[Curriculum Vitae] ON Landen_1.KODE = dbo.[Curriculum Vitae].Landcode RIGHT OUTER JOIN dbo.ContactHistorie2 INNER JOIN dbo.mailings ON dbo.ContactHistorie2.MailingID = dbo.mailings.MailingID INNER JOIN dbo.v_uniekeadressen ON dbo.ContactHistorie2.PersoonID = dbo.v_uniekeadressen.PersoonID INNER JOIN dbo.Adressen ON dbo.v_uniekeadressen.AdresID = dbo.Adressen.AdresID INNER JOIN dbo.Personen ON dbo.ContactHistorie2.PersoonID = dbo.Personen.PersoonID INNER JOIN dbo.Contacten ON dbo.ContactHistorie2.PersoonID = dbo.Contacten.PersoonID AND dbo.ContactHistorie2.relatieID = dbo.Contacten.RelatieID LEFT OUTER JOIN isis_interface.dbo.ID_NATIONALITEITEN ON dbo.Personen.NationaliteitsCode = isis_interface.dbo.ID_NATIONALITEITEN.KODE LEFT OUTER JOIN dbo.v_uniekeWerkGegevens ON dbo.ContactHistorie2.PersoonID = dbo.v_uniekeWerkGegevens.PersoonID LEFT OUTER JOIN dbo.v_PersoonOpleidingenUniek ON dbo.ContactHistorie2.PersoonID = dbo.v_PersoonOpleidingenUniek.PersoonID ON dbo.[Curriculum Vitae].CVID = dbo.v_uniekeWerkGegevens.CVID LEFT OUTER JOIN dbo.Landen ON dbo.Adressen.LandCode = dbo.Landen.KODE WHERE (dbo.ContactHistorie2.MailingID = @mailingID) ORDER BY CASE WHEN Adressen.LandCode = '6030' THEN 0 ELSE 1 END, CASE WHEN dbo.v_PersoonOpleidingenUniek.opleidingenSec = 'FDEW' THEN 0 ELSE 1 END, CASE WHEN dbo.v_PersoonOpleidingenUniek.opleidingenSec = 'FDR' THEN 0 ELSE 1 END, CASE WHEN dbo.v_PersoonOpleidingenUniek.opleidingenSec = 'FDGW' THEN 0 ELSE 1 END GO when this procedure is executed it will show a lot of columns. One of these columns is the column 'faculteit'. When a person, indicated by 'persoonID', is having more than one 'faculteitsjaar' (faculty year), these fields are combined. So when this persoonID has a record concerning the value FDEW and another record with FDG then the output of this procedure is FDEW / FDG. Now it is the task to adjust the procedure in order to only show the oldest faculty. It's is getting far too complicated for me, so I would like you guys to help me out in this one. Would be really appreciated kind regards pirates
View Replies !
View Related
Combined 2cols Into 1
create view vwchannel as select distinct s2.soptype, s2.sopnumbe , --internet orders/information center orders s2.custnmbr , --amazon.com orders s3,itemnmbr, sum(s3.quantity) from salestab s2 left outer join (select distinct soptype,sopnumbe,itemnmbr,quantity from salesdisttab) s3 on s2.soptype = s3.soptype and s2.sopnumbe = s3.sopnumbe where (s2.CUSTNMBR LIKE 'amazon%') AND ((s2.SOPNUMBE LIKE 'net%') OR (s2.SOPNUMBE LIKE 'inv%')) AND s2.soptype = 3 group by s3.itemnmbr,s2.sopnumbe,s2.custnmbr,s2.soptype go i m getting 70 rows in output which is correct but.. i have to combine sopnumbe and custnmbr into one column name as channel how can i do that? i tried like: case when (s2.SOPNUMBE LIKE 'net%' OR s2.SOPNUMBE LIKE 'inv%') then s2.sopnumbe when s2.CUSTNMBR LIKE 'amazon%' then s2.custnmbr end as channel but i m getting 0rows affected in ouput.. instead of 70rows... can anyone help me? thanks.
View Replies !
View Related
Convert A Time Field In The Select Statement Of The Query
Hi, I have a field called "Starting DateTime" and I want to convert into my local time. I can convert it in the report with the expression "=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Starting_DateTime.Value)", but that is too late. I want to convert it in the Select statement of the query. Can anyone help me please? Thx
View Replies !
View Related
CASE Statement Error: Cannot Convert A Char Value To Money
I am trying to write a Procedure in SQL 2005 that retreives a list of rows from the database. I pass to the procedure sorting parameters. This procedure works fine for all of the fields that I enter in the Order By, except for one. The field that does not work is a varchar(500) field (named Description). I am not sure what the problem is. The error that I am getting is: Msg 235, Level 16, State 0, Line 13 Cannot convert a char value to money. The char value has incorrect syntax. Attached is the T-SQL Code. The declare and set statements in the beginning are for informational purposes. Any help is appreciated. ------------------------------------------------------------------------------------------------------ declare @Match varchar(75) declare @NumRows int declare @StartPos int declare @OrderBy varchar(25) declare @OrderDir varchar(4) set @Match = 'dog% AND bark%' set @NumRows = 25 set @StartPos = 100 set @OrderBy = 'Description' set @OrderDir = 'ASC' WITH catitems AS ( SELECT ci.ID, ci.SupplierCode, ci.Description, ci.AUDIO_LINK, ci.SoundLength, ci.HighCost, ci.Channels, ROW_NUMBER() OVER (ORDER BY CASE WHEN @OrderBy='ID' AND @OrderDir='ASC' THEN CAST(ci.ID AS int) WHEN @OrderBy='SupplierCode' AND @OrderDir='ASC' THEN CAST(ci.SupplierCode As int) WHEN @OrderBy='Description' AND @OrderDir='ASC' THEN CAST(ci.[Description] As varchar(500)) WHEN @OrderBy='HighCost' AND @OrderDir='ASC' THEN CAST(ci.HighCost AS money) WHEN @OrderBy='Channels' AND @OrderDir='ASC' THEN CAST(ci.Channels AS smallint) END ASC, CASE WHEN @OrderBy='ID' AND @OrderDir='DESC' THEN CAST(ci.ID AS int) WHEN @OrderBy='SupplierCode' AND @OrderDir='DESC' THEN CAST(ci.SupplierCode As int) WHEN @OrderBy='Description' AND @OrderDir='DESC' THEN CAST(ci.[Description] As varchar(500)) WHEN @OrderBy='HighCost' AND @OrderDir='DESC' THEN CAST(ci.HighCost AS money) WHEN @OrderBy='Channels' AND @OrderDir='DESC' THEN CAST(ci.Channels AS smallint) END DESC) As RowNo FROM TableName As ci WHERE CONTAINS (ci.Keywords, @Match) AND ShowOnWeb=1 ) SELECT catitems.ID, catitems.SupplierCode, catitems.Description, catitems.AUDIO_LINK, catitems.HighCost, catitems.Channels FROM catitems WHERE RowNo BETWEEN @StartPos AND @StartPos + @NumRows -1 ------------------------------------------------------------------------------------------------------ Thanks, Aric
View Replies !
View Related
Combined 2 Data And Separate Them
I have a data grid with dropdownlist.the dropdownlist is populated with datas wth a sql statement with 2 combined datamy sql : SELECT NAME + CAST(ID as CHAR(10)) FROM TABLE1When i select a value from the dropdownlist, i need to separate the data, name and id into different columnshow do i do it?Is there a way to manipulate the sql to do such a thing?
View Replies !
View Related
Passing Variables From URL And DDL Combined
In our project users log in and are assigned a GUID. The GUID is stored as a session variable that is used for filtering what a user sees on a page/report etc. We have a report in which there are 2 parameters (Drop Downs). Drop Down 1 lists the Entities a user can see (this is filtered by the GUID that is passed to the backend) and this works fine. Drop Down 2 lists the products a user can see within Entity (this is filtered by the same GUID and also the selected value from DDL1.) Here€™s the dilemma, how to we pass 2 variables into DDL2, when one of the variables comes from DDL1, and the other is passed by the URL?
View Replies !
View Related
Syntax? - Searching Combined Columns
Dear GroupThe scaenario is as follows:FirstName and LastName are separate columns in the contact table and Iwant to be able to search e.g. for the FirstName and part of theLastName at the same time e.g. 'John A' should return 'John Adams'.Would be grateful if you can give me some hint as I don't seem to getit work.SELECT FirstName, Lastname FROM i2b_contact WHERE (SELECT Firstname +Lastname AS CName) LIKE 'John A%'Thanks very much for your help and efforts!Martin
View Replies !
View Related
Combined Orders Grand Total?
Hi, Im using Access and have created a query which adds up the grand totals of clients orders. However i want to be able to add up what each client has spent in total over any given time period. Basically in SQL logic terms: take all order totals from same client and add together order totals to produce a grand client spend total. Anyone help!? thanks
View Replies !
View Related
Errors With Combined Use Of Transactional And Merge Replication - SQL2005
I am investigating the feasibility of a configuration with 3 databases on SQL2005 DB_A is an OLTP database and serves up transactional publication pub_txn - with updateable subscriptions DB_B is a subscriber database which subscribes to pub_txn DB_B is also a publisher which serves up merge publication pub_merge DB_C is a subscriber database which pulls pub_merge =============================== Updates on DB_A are successfully replicated to DB_B Howvever, when DB_C pulls updates, it doesn't find the update sent to DB_B =============================== Updates on DB_B are successfully replicated to both DB_A and DB_C =============================== Updates on DB_C initially failed with the error Msg 916, Level 14, State 1, Procedure trg_MSsync_upd_course_type, Line 0 The server principal "repllinkproxy" is not able to access the database "DB_C" under the current security context. I then changed the login repllinkproxy to be a db_owner in DB_C I now get the error Msg 208, Level 16, State 1, Procedure sp_check_sync_trigger, Line 23 Invalid object name 'dbo.MSreplication_objects'. ================================= I have three questions as a result 1) Is there anything fundamentally wrong with what I am trying to achieve? 2) Why is update on DB_A not reaching DB_C 3) Why can't I update DB_C? Any suggestions gratefully received aero1
View Replies !
View Related
Multiple Rows Combined Into Onerow And Onecolumn Separated By A Special Character
Hi All :CREATE TABLE TABLEA(Person Varchar(20), Country Varchar(20), SubjectVarchar(20), Type Char(1))INSERT INTO TABLEA VALUES ('Einstein', 'Germany', 'Physics', 'P')INSERT INTO TABLEA VALUES ('Kant', 'Germany', 'Philosophy', 'Q')INSERT INTO TABLEA VALUES ('Kafka', 'Germany', 'Writer' , 'W')INSERT INTO TABLEA VALUES ('Aristotle', 'Greece', 'Philosophy', 'Q')INSERT INTO TABLEA VALUES ('Archimedes', 'Greece', 'Physics', 'P')INSERT INTO TABLEA VALUES ('Homer', 'Greece', 'Writer' , 'W')SELECT * FROM TABLEAI am on SQL 2000.I need an output where i have to have a resultset grouped on Type, butthe results in one row.In the resultset I needTypeP PersonType P Country, Type Q Person, Type Q Country, TypeW Person Type W Country---------------------------------------------------------------------------------------------------------------------Einstein:ArchimedesGermany:GreeceKant:Aristotle Germany:GreeceKafka:HomerGermany:Greece************************************************** *************I have written a puesdo-cursor code to do the same, but if there is away to do as a set operation, that would be greatPlease select as a whole and past in query analyser as the resultsetis all overlaid when i paste in this box.Thank youRS
View Replies !
View Related
How To TRIM
Hi I have the below paragraph in my datacolum called 'Description' Ansmann Powerline 5 Super Fast Charger, Microprocessor controlled desktop charger with discharge function for up to 5 NiCd/NiMH cells, For all NiCd and NiMH rechargable batteries including AA/AAA/C/D/9V, Negative Delta V control, faulty cell detection, defined predischarge, trickle charge, separate status indicator for every charging slot, ultra rapid charging, 230V AC. For UK use. Price for 1 Charger. How do I take only the sentence upto the first comma in my STORED procedure (ex:Ansmann Powerline 5 Super Fast Charger) Advance thanks Vaishu
View Replies !
View Related
Trim()
Okay basic question from the village idiot. How do I return only the date and not the time from a datetime column and also can I return only a set number of digits in a float column? Thanks in advance peter The Yak Village Idiot
View Replies !
View Related
Help Trim Or Len
Guys/Girls Have a small problem I have received a load of data in a flat file format and each column has dividend up with a comma. I have stuck the whole row in to its own column. What I want to do is take the first 12 characters of the row and put them in there own column. I think I need to use the Len function or the trim function, so can some body point me in the right direction on how to do this. Regards Lee
View Replies !
View Related
(((Trim(Nz
IN ACCESS UPDATE 14_Together_final_ICNs INNER JOIN 14_Together_UniqueSKUs ON [14_Together_final_ICNs].SKU = [14_Together_UniqueSKUs].SKU SET [14_Together_final_ICNs].sku_desc = [14_Together_UniqueSKUs].NewDesc WHERE (((Trim(Nz([NewDesc])))<>"")); THIS IS WHAT I TRY IN SQL SEVER BUT GET A DIFFERNET RESULT UPDATE [14_Together_final_ICNs] SET sku_desc = [14_Together_UniqueSKUs].NewDesc FROM [14_Together_final_ICNs] INNER JOIN [14_Together_UniqueSKUs] ON [14_Together_final_ICNs].SKU = [14_Together_UniqueSKUs].SKU WHERE (RTRIM(LTRIM([14_Together_final_ICNs].sku_desc))<> N'')
View Replies !
View Related
Left Trim
What is the syntax of left triming characters seperated by space. e.g elsie reed and i want elsie to go to one field and reed in another field i do not want to use numbers because they don't all have the same numbers but are all seperated by spaces
View Replies !
View Related
Trim String
In my report, i have a TextBox which retrieve the result from the database.I am using this code : =Fields!Category.Value.ToString().Trim(" ").TrimEnd(",").TrimStart(",") But i am encountering a problem,wherein,when the value contains '&' (ex. Jog & Hop) the result became Jog&Hop. What could be wrong with my trim??? Thanks!!
View Replies !
View Related
TRIM Question?
"select RTrim(columName) from tableName" it can change the stirng " abc" to "abc". But I want change the string "a bc" to "abc". Why the TRIM cannot work ? How do I do ?Thanks.
View Replies !
View Related
Trim Character
OK, the problem is like this; say the table is sumthing like this i have a table 't3' has 3 fields id(number,2) date(date/time) text(varchar,200) eg. id date text ----------------------- 01 05-jul-07 abcdefghijkl (i want to get rid of the first 6 characters i.e. abcdef) i want to display 'text' field from t3 table but with the first 6 characters trimmed/removed (i want to get rid of the 6 first characters) so i did: select LTRIM(text,6) from t3 but the above code didn't work because LTRIM only trims spaces NOT characters... Is there any way that i can trim off the first 6 characters from every record in the 'text' field?
View Replies !
View Related
Trim Space From Some Fields
I have a table with a keyword field. In this keyword field some of the entries have a space before the word and some don't. How can I remove all of the spaces from the fields that have them and not trim from the ones that don't.
View Replies !
View Related
Trim Zero Out (007500,190000)
hi I have this field invoice# 007500 190000 000080 I need to run a query to trim the left zeros out and have the following output invoice# 7500 190000 80 how can I do that in sql select ... or with update table set invoice#=..?? Thanks for your help Al
View Replies !
View Related
Optimize & Trim Query
I’m trying to optimize the following view so that it runs faster and to trim the code where possible. It runs for 1:57mins. Is that good or it can run much faster than that? Where do l start? It’s a view for a report. Please help l’m running on SQL 2000 CREATE View SalesTest AS SELECT dbo.Loan.loan_No AS [Loan No], dbo.Customer.customer_No AS [Customer No], dbo.Customer.first_Name + ' ' + dbo.Customer.surname AS Customer, dbo.Employer.employer_Name AS [Employer Name], dbo.Loan.store AS [Store No], dbo.Store.store_Name AS Store, dbo.Region.region_Description AS Region, dbo.Financier.financier_Short + dbo.Term.term_Description AS Product, dbo.Loan.date_Issued AS [Transaction Date], dbo.Loan.capital_Amount AS [Capital Amount], dbo.Loan.interest_Amount AS [Interest Amount], dbo.Loan.interim_Interest_Amount AS [Interim Interest Amount], dbo.Loan.interest_Amount + dbo.Loan.interim_Interest_Amount AS [Interest2 Amount], dbo.Loan.insurance_Amount AS [Insurance Amount], dbo.Loan.admin_Fee AS [Admin Fee], dbo.Loan.total_Amount AS [Total Amount], dbo.Loan_Type.loan_Type_Description AS [Loan Type Description], dbo.Loan.user_Changed AS [User], dbo.Loan.first_Payment AS [First Payment], dbo.Loan.monthly_Payment AS [Monthly Payment], dbo.Loan.repayment_Period AS [Repayment Period], dbo.Loan.outstanding_Amount AS [Outstanding Amount], dbo.Loan.last_Payment_Date AS [Last Payment Date], dbo.Status.status_Description AS Status, CONVERT(Char(3), dbo.Loan.loan_No, 1) AS Company, dbo.Customer.physical_Address1 AS Mine1, dbo.Customer.physical_Address2 AS Mine2, dbo.Customer.physical_Address3 AS Mine3, dbo.Loan.maturity_Date AS [Maturity Date], dbo.Agent.agent_Short AS Agent, dbo.Financier.financier_Short AS Financier, dbo.Loan.product AS [Loan Product], dbo.Deduction_Detail.teba_Account_No AS [Teba Account No] FROM dbo.Loan INNER JOIN dbo.Customer ON dbo.Customer.customer_No = dbo.Loan.customer_No INNER JOIN dbo.Status ON dbo.Status.status = dbo.Loan.status INNER JOIN dbo.Store ON dbo.Store.store = dbo.Loan.store INNER JOIN dbo.Product ON dbo.Product.product = dbo.Loan.product INNER JOIN dbo.Product_Type ON dbo.Product_Type.product_Type = dbo.Product.product_Type INNER JOIN dbo.Financier ON dbo.Financier.financier = dbo.Product_Type.financier INNER JOIN dbo.Term ON dbo.Term.term = dbo.Product.term INNER JOIN dbo.Employer ON dbo.Employer.employer = dbo.Customer.employer INNER JOIN dbo.Region ON dbo.Region.region = dbo.Store.region INNER JOIN dbo.Loan_Type ON dbo.Loan_Type.loan_Type = dbo.Product_Type.loan_Type INNER JOIN dbo.Agent ON dbo.Agent.agent = dbo.Product_Type.agent INNER JOIN dbo.Deduction_Detail ON dbo.Loan.customer_No = dbo.Deduction_Detail.customer_No AND dbo.Loan.deduction_No = dbo.Deduction_Detail.deduction_No WHERE (dbo.Loan.outstanding_Amount <> 0)
View Replies !
View Related
Trim Blanks From Name Field
Can someone help me with this little problem ? I have a table that has first & last name fields. They're each 20 bytes long. I would like to remove the trailing blanks from the fields and have just null following the data .. Is that possible ? Then when I send the file to someone, they won't have to strip out blanks when combining the names together. OR 2nd choice ... Create a temporary work file and copy the data without trailing blanks to the new work file. I've looked at RTRIM, but can't seem to get it right. Thanks for any help.
View Replies !
View Related
|
TRACKER
