How Does Full Name Field Split To First Name And Last Name Field?
I 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!
View Complete Forum Thread with Replies
Related Forum Messages:
Split A Field
Whats the best way to do the following?Field1 in Table1 contains numbers and characters seperated by #Examples: aaa#01, kjhkjhjh#21 and jlkjlkj#123How can I create two new fields in Table1, one containing what is tothe left of the # and the other what is to the right?Regards,Ciarán
View Replies !
How To Split The Field In The Sql Query?
I have the field LocationID (string)which has values like "AZ001","AZ002","IN002","IN004" first 2 will be always alphabets and remaining 3 will be numbers, I want to split it like "AZ" ," 001" "AZ","002" "IN" "002" "IN" "004" now i will populate dropdownlist with unique values of "AZ" "IN" according to first dropdownlist how i will populate second dropdownlist? So how to write sql query for splitting? and then populating the dropdownlist ?
View Replies !
How To Split A Field Into Two Fields
I have the following fields in table A: GL_ID| Date |GL_Name_VC | Amount Period_TI|Year_SI =============================================== 1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005 =============================================== 1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006 =============================================== 1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006 =============================================== 1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006 The above database is running on Microsoft SQL Server 2000 and i would like to query for a report that looks something as below: Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance =============================================== 01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000 01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0 01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0 01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |6,000 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?
View Replies !
How To Split A Field Into Two Fields
I have the following fields in table A: GL_ID| Date |GL_Name_VC | Amount |Period_TI|Year_SI =============================================================== 1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005 =============================================================== 1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006 =============================================================== 1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006 =============================================================== 1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006 The above database is running on Microsoft SQL Server 2000 and i would like to query for a report that looks something as below: Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance ===================================================================== 01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000 01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0 01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0 01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |16,000 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?
View Replies !
Can I Split A Field Based On A Character?
Here's a question for the SQL gurus out there: I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be 8060 8060,4450 8060,4123,3245 Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example CODE1=8060, CODE2=4450, CODE3=NULL. I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field? Any help very greatly appreciated. Thanks, Simon.
View Replies !
Split Up Comma-delimited Field
I have a row in a SQL table that has 4 numerical values, separated by comma. I'd like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas. Any ideas how I could do this in T-SQL?
View Replies !
Split One Field In Multiple New Rows
HiHo, just a beginners question: I have the following row with 2 fields: Field 1: Task A Field 2:´ 1;2;3;4 The number of semicolon divided elements in Field 2 is variabel. I would like to create new rows like: Row 1 Field 1: A Field 2: 1 Row 2 Field 1: A Field 2: 2 Row 3 Field 1: A Field 2: 3 Row 4 Field 1: A Field 2: 4 I think I should use a Foreach Loop. But I don't exactly how to do it? best regards Chris
View Replies !
Conditional Split On Field In Csv File
I know this should be simple but I can't figure it out. I am reading in a csv file to a conditional split task, all I want to do is split the file based on a field. Some values in field will have a suffix say ABCD while others wont. So my conditional split says Right(FieldA,4)=="ABCD" which then splits file in two directions or at least it's meant to. Problem is that it does not work. I think it has something to do with the field type in the csv file although I have tried using a Data Conversion task but to no avail all the field values with ABCD suffix are ignored by my conditional split and head off the same way as other values. Funny thing is is that if I manually add a value to the file with a suffix of ABCD and run task again then the conditional split works on the manually added row and all rows with suffix of ABCD. It's like it does not recognise previous values as string until one is added manually. Thanks
View Replies !
Split Address Into Multiple Field
i want to write a code that can split the addresses into multiple fields. anyone can point me out where to start? lets say i have "12 north plaza boulevard apt.16" and i want it to become: address_number 12 suffixA North street_name plaza blvd suffixb apt16
View Replies !
One For The SQL Gurus: Split A Delimited Field Into Rows
Hi. I'm trying to write an SQL Query that will take a delimited field and return each item as a row. Example Take the AuthorizedRoles and TabID fields from the Tabs table AuthorizedRoles TabID 0;11;__________1 0; 15 ;17;______6 -2;____________7 I would like to return a unique record for each Authorized Role AuthorizedRole TabID 0____________1 11___________1 0____________6 15___________6 17___________6 -2___________7 Any ideas? Cheers Dave
View Replies !
Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert
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 mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2))) 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. With refresh_shipping_sched .ActiveConnection = CurrentProject.Connection .CommandText = "spRefresh_shipping_sched" .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue) .Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option) .Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "") .Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "") Set rs_refresh_shipping_sched = .Execute End Please help
View Replies !
Informix Date Type Field To SQL Server Datetime Field Error
I am trying to drag data from Informix to Sql Server. When I kick off the package using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP errors on two fields from Informix which are date data ....no timestamp part I tried a couple of things: Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed. Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them as datetime but it failed. What options do I have that will work?
View Replies !
Odbc - Binding Sql Server Binary Field To A Wide Char Field Only Returns 1/2 The Daat
Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn
View Replies !
Retrieving The Description Field Or SQL Comment Field From A View
Hi, I have managed to use the INFORMATION_SCHEMA.TABLES to retrieve a list of all my views. I was wondering if it was possible to retrieve the Description or SQL Comment field from a view via ADO.NET, which can be found in the Enterprise Manager Design View properties (right click - properties) I want to hopefully utilize this field for dynamic page titling of a datagrid bound view. Many Thanks in anticipation!
View Replies !
Multiple Foreign Keys On Same Field, Based On Other Field
I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
View Replies !
Create Date Field From Substring Of Text Field
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
View Replies !
Separating One Field Into Two Fields Based On A Character In The Field
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?
View Replies !
Access Memo Field To SQL Server Text Field
Hi, I'm importing an Access database to SQL Server 2000. The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all). I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data. Is this some sort of an encoding problem that arose during database import? I would appreciate any pointers.
View Replies !
Export Access Memo Field To SQL Text Field
Hi, Can anyone point me any solution how to export a MEMO field from an Access database to a TEXT field from an MS SQL Server 2000. The import export tool from SQL server doesn't import these fields if they are very large - around 9000 characters. Thanks.
View Replies !
Grouping By One Field And Combine Data Of Another Field In SSRS
Hi all experters, Please suggest how to build the report in below case: Raw data: ID Member Functions 1 Alan A 1 Alan B 2 Tom A 2 Tom B 2 Tom C 3 Mary D 3 Mary E Report Shows: ID Member Functions 1 Alan A,B 2 Tom A,B,C 3 Mary D,E I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID. Any good suggestion? Thanks in advance, Steve Wang 2008/4/2
View Replies !
MS Access Memo Field To SQL Server Text Field
Hi all, i've a reasonable amount of experience with MS Access and less experience with SQL Server. I've just written an .NET application that uses an SQL Server database. I need to collate lots of data from around the company in the simplest way, that can then be loaded into the SQL Server database. I decided to collect the info in Excel because that's what most people know best and is the quickest to use. The idea being i could just copy and paste the records directly into the SQL Server database table (in the same format) using the SQL Server Management Studio, for example. Trouble is, i have a problem with line feed characters. If an Excel cell contains a chunk of text with line breaks (Chr(10) or Chr(13)) then the copy'n'paste doesn't work - only the text up to the first line break is pasted into the SQL Server database cell. The rest is not pasted for some reason. I've tried with MS Access too, copying and pasting the contents of a memo field into SQL Server database, but with exactly the same problem. I've tried with 'text' or 'varchar' SQL Server database field formats. Since i've no experience of using different types of databases interacting together, can someone suggest the simplest way of transferring the data without getting this problem with the line feeds? I don't want to spend hours writing scripts/programs when it's just this linefeed problem that is preventing the whole lot just being cut'n'pasted in 5 seconds! cheers Dominic
View Replies !
Convert Field From VarChar To Int With Speical Characters In Field
Hello, I have a table with a column that is currently a varchar(50), but I want to convert it into an int. When I try to just change the type in design mode I get an error that conversion cannot proceed. When I look at the field it appears some of the entries have special characters appended at the end, I see a box after the value. How can I remove all speical characters and then convert that field to an int? Also I tried the following query which did not work as well, same error about conversion. UPDATE myTable SET field = CAST(field AS int)
View Replies !
Using A Regular Expression In MSSQL 2005 To Find Patterns Buried In A Field Full Of Misc Data
Thanks 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'
View Replies !
Adding A New Field To A Table With Image Field
Hi, I have a concern about adding a new field to a table with image field - which is huge. Will there be a problem with some databases, where they have a hard time locating data correctly after such a large field? Previously this happened to me, and what was advised to put all the big fields at the end of the table. Thanks.
View Replies !
Parsing A Field Into Another Field Using Stored Proc
Hi,I am running SQLServer2000 with ASP.NET. I have 3 fields:DATE_TIME (date time 8)DATEF ()TIMEF ()...when my default.aspx page is called, i need to execute a stored procedure that will parse my DATE_TIME field by the space in between, then update my DATEF and TIMEF fields with the appropriatevalues. NOw i have 2 fields to query from instead of 1. How would i write a SQL expression to do this? and what datatypes should the DATEF and TIMEF fields be?thxj
View Replies !
Question About Converting Bigint Field To Int Field
We made a poor decision a long time ago when designing our databasestructure. We used bigint data types as the identity keys for many ofour base tables. For many reasons I would like to change these fieldsto int at the largest. The largest data in these fields is around200,000. I know that int can easily store this.What should I be worried about when changing these fields from bigintto int? If anything. Your help is appreciated. I did severalsearches without much luck.
View Replies !
Update SQL Field With Stripped Data From Other Field
Not a SQL guy but can do enough to be dangerous :)Trying to update a record. We have records that have a field with datasurrounded by some comment text such as *** Previous Public Solution*** Start and *** Previous Public Solution *** End . What I am tryingto do is write a SQL statement that will:Check that field C100 = TICKET0001 (to test with one record beforerunning on whole db)Check that field C101 is = ClosedCheck that field C102 is nullCopy field C103 data to field C102 and strip out any words such as ***Previous Public Solution *** Start and *** Previous Public Solution*** endThanks for any help!Kevin
View Replies !
Using Lookup And Display Only Field As Control Field
I am not sure If I can do this with a lookup, but what I would like todo is perhaps use lookup to retrieve a control date from anunassociated table to control what date is entered in another table.For example :the main table , table 1 has many entries with a field called date_enter which is the date the record was entered.table 2 has a control_dateIf the date entered in table 1 is less than or = to the control date wewant to give the user a error message.I am thinking of using display only field and lookup to set itWould need to have the date value from the control table available tothe active table of table 1 when entering the the date_enter.However as there is no join field between the two tables am not surehow to do it. Was thinking might have to add a key field that wasallways null and in the BEFORE EDITADD EDITUPDATE section set it sothat the key would be nullAm using Informix 5 , Any help would be apprecia
View Replies !
Fail To Update Field With A Field Uniqueidentifier
Hi all, I have a problem about a query to update a table UPDATE Email SET EmailDT='31 Mar 2004' WHERE Idx={BDF51DBD-9E4F-4990-A751-5B25D071E288} where Idx field is a uniqueidentifier type and EmailDT is datetime type. I found that when this query calling by a VB app. then it have error "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" and i have tried again in Query Analyzer, same error also occur, the MS SQL server is version 7. Please help. thanks.
View Replies !
Index In Calculated Field From A Ntext Field
For some reasons I have to use a ntext field for both small strings like "10" and large binalry files. I need to sort the field to some extend to present the small strings on a sorted nice way - answers to " What country are you from" etc. To trick the sorting I use a calculated field: ORDER BY RSort - where Rsort is: convert(varchar(4), RD.response) as RSort It works but put a high load on the SQL server when the number of responses increases. I though of making a non clustered index based on the calculated field, but is not sure that it will work as intended. What do I do. The last thing would be to change the ntext to vchar(3800) or something like that. :confused:
View Replies !
How To Change Integer Field To Identity Field.
I have an integer column(not null) in a table. how can i change that to identity column through query analyzer, through a script, b'cos i have to apply this to number of databases. I tried this, but got an error: ALTER TABLE table_name alter column company_id int IDENTITY ERROR: Incorrect syntax near the keyword 'IDENTITY'. any help is welcome.
View Replies !
Compare Date Field To Text Field
Hi, I am very new to using SQL. Our department usually uses Brio to query the various databases under our control. However, I have recently come against a problem that prompted me to create a custom SQL query which works well as far as it goes. My problem is looking for specific conditions in billing information I receive monthly. I would like to compare on of the date fields contained in the database with a field in the form of YYYYMM (200710, for October 2007) I have created a custom column generator that forms a date from the YYYYMM. I would like, however, do the translation on the fly and make the comparison during the query. The problem is that query without the date check returns a mass of data, only about 1 percent of which is what I want. The beginning of the SQL query looks like this: FROM From.T_Crs_Tran_Dtl WHERE T_Crs_Tran_Dtl.Crs_Bill_Yr_Mo IN ('200710', '200711', '200712') AND ((T_Crs_Tran_Dtl.Crs_Cde IN ('1G', '1V') AND (T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND (T_Crs_Tran_Dtl.Prev_Stats_Cde IN (' ', 'TK', 'TL') AND T_Crs_Tran_Dtl.Cur_Stats_Cde IN ('TK', 'TL') AND T_Crs_Tran_Dtl.Std_Tran_Typ_Cde='B') OR (T_Crs_Tran_Dtl.Prev_Stats_Cde='UN' AND T_Crs_Tran_Dtl.Cur_Stats_Cde='XX' AND€¦ It is the €œ(T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND€? part of the query that is just plain wrong. The business part of this statement takes the YYYYMM field and turns it into a date which is the last day of YYYYMM. I hope someone out there can help me with making this comparison. I appreciate your help. Bill
View Replies !
Need Help: Count A Field Based On Field Criteria
Hi, I can't seem to get my head around this, hopefully someone can help. In my query I have a few fields, which I would like to get the number of rows based on certain criteria. For example I have a gender field, and I would like to get the number of females or males from the query. I have been trying the following method, which uses the Sum and Iif functions: Code Block Sum(IIf(Fields!gender.Value = "Female", 1, 0)) This does bring back a figure, but it's not correct. My query brings back 46 rows, yet the expression only brings back a figure of 24. I have even tried doing a query using CASE statements, bringing back just one row with all the correct figures in. But if I then add a textbox, with the expression '=Fields!gender.Value' it also brings back 24, yet the fields actual value in the query is 46. What is causing this problem? Is there a way around this? Thanks
View Replies !
Selecting Field And Sum Of Field Issues Error.
With this query: SELECT Table1.1ID, Table1.Field1, Table2.2ID, Table2.Field1, Table3.3ID, Table3.Field1, sum(Table1.Field1), sum(Table2.Field1), sum(Table3.Field1) FROM Table1 INNER JOIN Table2 ON Table1.1ID = Table2.2ID INNER JOIN Table3 ON Table2.2ID = Table3.3ID I get this error: Column 'fieldname' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. Is it because you can't do what I'm trying above or am I missing something? Thank you! --PhB
View Replies !
|
TRACKER
