SQL Server 2008 :: Splitting A Field In The Middle?
Mar 19, 2015I have a field which looks like "LastName, FirstName (DOB: 01/01/1900)"
How do I get the "01/01/1900" between ":" and ")"
ADVERTISEMENT
SQL Server 2008 :: Function To Check Name Format (Last / First / Middle)
May 27, 2015I have to figure out the items that Legal Name implies individual but Legal Entity Structure indicates a incorporation type. In this sample, you can see Alexander, Justin N. is my target. But my problem is how should I use a query to figure out which one is a individual's name? How should I write a function to check the name format (Last, First Middle)?
Legal Name ////////////////////////////////////// Legal_Entity_Struct
S & H Farm Supply, Ltd.////////////////////////////Company
F.M.Abbott Power Equipment,Co.///////////////Company
Ray's Dixie Chopper, Inc.////////////////////////// Company
Alexander, Justin N. ///////////////////////////////// Company
Alameda Power Equipment, Inc.//////////////// Company
[Code] .....
SQL Server 2008 :: Not Finding A Record With A Space In The Middle Of String
May 19, 2015Ok following scenario
create table dbo.ADAssets
(Computername nvarchar(max))
insert into dbo.ADAssets
values
('AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565')
So if execute the following
select Computername
from dbo.ADAssets
where Computername like
'AIRLBEOF3565%'
I get both records,but if I do this
select *
from dbo.ADAssets
where Computername in
(
'AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d',
'AIRLBEOF3565'
)
I only get AIRLBEOF3565
So the big picture is that I need to compare 2 tables to find records that match & don't but that I get matches that shouldn't be & matches that aren't.
SQL Server 2008 :: Splitting Param Array Into Groups Of Four With Offset
Feb 25, 2015I'm getting a file that essentially has a parameter array: a specific like would have three known values, and then x number of groups of four parts;
The question is how to handle logical groups of parameters; i though maybe by using a modulous on their itemnumber ...
So I can easily use DelimitedSplit8K to definitively find the first thee values, but how do I dynamically get X number of groups of four; eventually i need the first three fields combined with each quadgroup.
So a rough example of the desired output is this:
this:
'03,0000001,USD,010,81257946,,,015,121809761,,'is split into rows with the first three columns included in each row:
030000001USD01081257946NULLNULL
030000001USD015121809761NULLNULL
Here's a setup i've put together:
IF OBJECT_ID('tempdb.[dbo].[#AccountIdentifier]') IS NOT NULL
DROP TABLE [dbo].[#AccountIdentifier]
GO
CREATE TABLE [dbo].[#AccountIdentifier] (
[AccountIdentifierID] INT IDENTITY(1,1) NOT NULL,
[DateReceived] DATETIME NULL DEFAULT getdate(),
[RecordCode] VARCHAR(3) NULL,
[Code] ....
Get Middle Initial From A Name Field
Nov 15, 2013I need to display the middle initial from a name field that contains the last name, comma, and the middle name or initial.
Example data:
Jane,Smith Ron
John,Dow L
Mary Jane,Dow Welsh
The result I am looking for is to capture only the first letter of the middle name. In this data example, I would need to display the following on a separate column:
R
L
W
Updating Data In Middle Of Field
Sep 6, 2013I have a table with 100,000 addresses and I need to change 'street' to 'st', 'avenue' to 'ave' and 'road' to 'rd'. These words can appear anywhere in the field. example
10 Main street & Washington avenue
Trying to get it to be
10 Main st & Washington ave
SQL Server 2008 :: Selecting Field That Contains Data From Another Field
May 28, 2015We have a stock code table with a description field and a brand field - when the data was entered, some of the records were entered with the brand field in the description field.
ie.
Code Description Brand
ABC1 BLANK DVD SONY
ABC2 SONY BLANK DVD SONY
what I need to do is identify where the Brand is in the Description field ...
I have tried ;
select * from Table
where Description Like Brand
not very successful.
Splitting A Field Into Two
Jul 20, 2005I have a field that contains codes likefhj#asdskjjljlj#12And so on.What I want to do is create two new fields (field1 and filed2) thatsplit the original filed at '#'If a field does not contain '#' I would like its entire contents to besaved in field1.Also how do I ensure that I save these changes?Thanks fo any help in advance.Regards,Ciarán
View 2 Replies View RelatedNeed Help Splitting Contents Of A Field In T-Sql
May 22, 2006I have a table with about 2 million records.
One of the fields has data seperated by a comma. I need to be able to grap the data for each record and split those items into their own table in seperate rows. This is easy in Asp but the page will timeout before it can process all the records.
Any Ideas?
Thanks,Rick
Splitting A Text Field
Feb 1, 2001hi,
I am trying to split a text field .
what I want to do is -
split a text field and insert chars 1 to 8 in one field and then from 9 to 20 in another and so on.
Can someone help me in solving this.
TIA.
PD
Problems Splitting Name Field
Sep 1, 2006I am reading in data from a legacy system where name data is in one field. The single field includes last name, first name, middle initial(sometimes), and suffix(sometimes).
Due to the optional components it is a bear. I am using a fuzzy lookup on the suffix and derived columns for the first, last, and middle initial. The first name and suffix work, but I'm having trouble with the last and middle initial. The following is the formula I'm using for both. The first name formula is causing an error, and the middle initial is only populating if the suffix exists. If anyone has insight, it is appreciated.
FirstName:
TRIM(SUBSTRING([ARPNAME-T],FINDSTRING([ARPNAME-T]," ",1) + 1,FINDSTRING([ARPNAME-T]," ",2) - FINDSTRING([ARPNAME-T]," ",1) + 1))
MiddleInitial:
SUBSTRING(TRIM([ARPNAME-T]),FINDSTRING([ARPNAME-T]," ",2) + 1,(LEN(TRIM([ARPNAME-T])) - FINDSTRING([ARPNAME-T]," ",1) - LEN(PSUFFIX)))
SQL Syntax Trouble (Splitting A Field In Two)
Apr 12, 2007I am able to use the following SQL Statement in Access, but when I try to use it in a View or Stored Procedure for SQL Server 2000, it says the function InStr is not a valid function. Would someone be able to assist me with modifying this query to work in SQL Server 2000. This is Spliting a field called EName into two fields called FName and LName.
SELECT EmployeeID, CountryCode, Branch, SSN, Title, HireDate, Status,
RIGHT(EName, LEN(EName) - InStr(EName, [, ]) - 1) AS FName, LEFT(EName, InStr(EName, [, ]) - 1)
AS LName
FROM dbo.Staging_Employees
Thank You,
Wayne
Splitting Name Field To FName And Last Name In Package
Apr 2, 2007Hi
I have a customer data in a flat file (*.csv). It has a field "Name" which has data as James Smith. I want to load the dat in the dimenson table as fields FName="James" and LName="Smith". What type of data transformation should i use and how should i make it happen?
thanks,
chamajid
Splitting One Data Field With Hyphens To Separate Records
Feb 16, 2004I receive a file that will have hyphens between data items such as
123-aed-edr-45r-ui9
1-ed3-45r-rrr-98u
I need to split the values to load into a table that will hold the 5 separate data itens. The fields will always have the hyphens but could be different lengths. Any idea on a best approach to split this in tsql
Splitting Out Names From One Field Delimted By Comma And A Space
Apr 28, 2004have a field in a table that has combined lastname,firstname and middle name like
combs,albert mike
woods-athere,jane alice
The last and first name are separated by a comma, and the middle name by a space
I need in tsql to split them to last name,first name and middle name
I used below
select substring (longname, 1, patindex( '%,%' , longname) -1 ) 'firstname',
substring (longname, patindex( '%,%', longname) + 1, len(longname)) 'lastname',
substring (longname, patindex( '% %', longname) + 1, len(longname)) 'middlename'
FROM Demographic_staging
I get the names all split, but I get the middle name with the first name. How can I limit the first name to recoginze the comma, but stop at the space
SQL Server 2008 :: Query The Same Field Name
Oct 20, 20153 table join query
Problem : city name the same
SQL Server 2008 :: Field As Group Header?
Jan 29, 2015I have a table returning results like that
Row1 ||Row2 ||ERPID||ParentID||LevelID||Category||SubCategory||DDate ||publish
1 ||1 ||10152159||1015 ||2159 ||LOCTITE ||LOCTITE1||29/01/2015 12:10||0
1 ||2 ||10152134||1015 ||2134 ||LOCTITE ||LOCTITE2||29/01/2015 12:10||0
1 ||3 ||10152157||1015 ||2157 ||LOCTITE ||LOCTITE3||29/01/2015 12:10||0
2 ||1 ||10062003||1006 ||2003 ||COMPUTER||COMPUTER1||29/01/2015 12:10||1
[code]....
But I want to look like that
Row1||Row2||ERPID||ParentID||LevelID||Category||SubCategory||DDate||publish
1||1||10151015||1015 ||1015||LOCTITE||||29/01/2015 12:10||0
1||1||10152159||1015 ||2159||LOCTITE||LOCTITE1||29/01/2015 12:10||0
1||2||10152134||1015||2134||LOCTITE||LOCTITE2||29/01/2015 12:10||0
1||3||10152157||1015||2157||LOCTITE||LOCTITE3||29/01/2015 12:10||0
2||2||10061006||1006||1006||COMPUTER||||29/01/2015 12:10||1
[code]....
SQL Server 2008 :: Set Null Does Not Work For More Than One Field
Apr 23, 2015I have the Person table with the following fields:
Id_Person
Nm_Person
Id_AddressResidential
Id_AddressCommercial
Another table called Address with the following fields:
Id_Address
Ds_Street
I want to make the relationship between these tables so that when deleting a related field address in Person is set to null.the SQL Server allows me to make this relationship in only one field.A person can live and work in the same place. If I delete her address, I want the two Individual fields are set to null.
SQL Server 2008 :: How To Add Time From One Field To Date From Another
Nov 2, 2015If I have a datetime field, RequestDate, and a time field, DecisionTime, how do I add just the date portion of RequestDate to DecisionTime?
View 6 Replies View RelatedSQL Server 2008 :: Populating UserID Field That Comes From One Of 2 Databases
Feb 12, 2015I have 2 databases: one for our intranet & another for our internet website.In the intranet database, we have a table called "Clients". A new client can be added in one of 2 ways:
1) One of our employees manually adds the client via the intranet
2) A new customer subscribes to our services via our website, thus becoming a new "client" (when subscribing online, we also add a record to an "Accounts" table located in our internet website database, but we'd also like to add a record to our intranet's Clients table as well).
In the client table is a field called "CreatedBy" which expects the UserId of whoever created the account. Again, this UserId can belong to either an employee via the intranet or a new customer via our internet website. how do I distinguish where the UserId in the dbo.Clients.CreatedBy field is coming from?
My 1st thought was to append a negative to the CreatedBy UserId# if it came from a customer via the website, but that just seemed too quirky (i.e. if an employee added a client and his UserId was '10', the Clients.CreatedBy would be '10'. If a customer subscribed via the website, the Clients.CreatedBy would be '-10'. Where customers can once again sign up online. So, I need a way to keep these databases separate but keep track of which db a user's coming from when we create a new Client record.
SQL Server 2008 :: How To Change Columns Based On Another Field Value
Jun 10, 2015I would like to pull data from two seperate columns based on the vaule for MakeFlag. So if MakeFlag = 0 I would like the description to show but anything else I would like catalog description to show up.
DECLARE @MyVari varchar(20)
SELECT [ProductID]
,[prod].[Name]
,[ProductNumber]
,[MakeFlag]
[Code] ....
SQL Server 2008 :: Copying ID Number To Another Field In Table
Jun 26, 2015I need to have a table that has a primary key
CREATE TABLE [dbo].[testing](
[tid] [int] IDENTITY(1,1) NOT NULL,
[sometext] [varchar](150) NOT NULL,
[idcopied] [varchar](50) NULL,
CONSTRAINT [testing_PrimaryKey] PRIMARY KEY CLUSTERED
..and eveytime I add 'sometext' as another row, the tid # needs to be duplicated to idcopied field
insert into [testing] (sometext,idcopied) values ('some junk',@@identity)
???
SQL Server 2008 :: Replication Error - Field Size Too Large
Feb 1, 2011I've got two databases on the same server and replicate some tables from one database to another.The replication is configured so not to drop the table if it exists, but to delete the data based on the filter if one exists. There are two tables on the subscriber that have some extra columns.
I get "field size too large" error when trying to replicate them. Is there a workaround without having to make the publisher and the subscriber tables identical by schema?
SQL Server 2008 :: Update Column With Field Of Imported File
Mar 24, 2015I have a table with 201 columns . I am importing 200 columns from a file using DFT. I want update the 201th column with the fileId of the file that just imported. I am storing the fileId of the file in varFileID .
How do I go back and update the 201th column ( column name sfileId) with the varFileID value?
I can use Execute SQL Task but how will I know it's the records of the files that I just imported not other rows.
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
SQL Server 2008 :: Using SSIS To Pull Oracle Data Into XML Field
Jul 1, 2015We have a custom web C#/SQL2K8R2 workflow application that I need to pull Oracle data into a varchar(max) field as an XML DOM document. I have no problem pulling the Oracle data using OLEDB, but I'm not sure how to create the XML DOM doc. Once I get it into the DOM doc, I then need to assign metadata about the XML DOM doc and insert it all into a staging table:
CREATE TABLE [stg].[EtlImports](
[EtlImportId] [int] IDENTITY(1,1) NOT NULL,
[EtlSource] [varchar](50) NOT NULL,
[EtlType] [varchar](50) NULL,
[EtlDefn] [varchar](max) NULL, --Either a SQL statement or path to file on disk
[EtlData] [varchar](max) NULL, --BLOB field to hold the XML data or FILESTREAM path to file on disk
[EtlDateLanded] [datetime] NOT NULL,
[EtlDateProcessed] [datetime] NULL,
[EtlStatus] [varchar](50) NULL,
[Comments] [varchar](4000) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I will have a separate SSIS package to pull the [XML/File] field and process the data into the workflow tables. Is there a wasy I can use the ADO Record-set Destination task to accomplish this, or do I have to create a custom C# script to create the XML DOM Doc?
SQL Server 2008 :: Returning Unique Rows - Read Date Field?
Jul 26, 2015I have the following query:
Select p.Id [SenderId], p.Username, up.PhotoId,
CASE
WHEN mr.ReadDate is null then 1 -- New message
ELSE 0 -- Message has been read
END AS NewMessage,
p.LastLoggedIn, p.LoggedIn
[Code] ....
The above query returns me all messages (inbox functionality) that have been sent to mr.ReceipientId, the issue I have is when I send another email to the recipient the readdate field will be null, and the other emails linked to the recipient which have also been sent via me will have a readdate date. This causes duplicate rows to appear due to the case statement, I'm trying to figure out if / how it is possible to only display the one row per conversation and set newmessage to 1 if there is an un-read message otherwise show 0 ?
So instead of showing this:
2Person102015-07-26 17:07:24.9370
2Person112015-07-26 17:07:24.9370
I'm trying to get it to look like this:
2Person112015-07-26 17:07:24.9370
Or if we have no new mail (read date is not null) then it will appear like this, and I can confirm when there is no new mail it works as expected.
2Person102015-07-26 17:07:24.9370
SQL Server 2008 :: Turn Field Values Into Comma Separated List
Aug 11, 2015I have about 100 K records of the form below in Example 1 and I would like to turn them into the form of Example 2, basically turn the entries in field2 into a coma separated list of values sorted by field1.
Example 1:
field1_field2
1_____a
1_____b
1_____c
2_____f
2_____g
and I would like to get it in the form
Example 2:
field1_field3
1_____a,b,c
2_____f,g
SQL Server 2008 :: Statement Error Out Due To Source Field Being Unique Identifier
Sep 22, 2015I have a field I am trying to bring into a SQL statement
,ISNULL(Convert(nvarchar(50),OPP1.OriginatingLeadId),'') AS 'OriginatingLeadId'
I get this error message
Conversion failed when converting from a character string to uniqueidentifier.
the field specs' originatingleadid is attached ....
SQL Server 2008 :: Concatenating Multiple Text Fields Into One Field In Another Table
Oct 7, 2015SQL code for the following? (SQL Server 2008 R2 - SQL Server 2012).
I have Table1 Containing two fields with the below entries
VehicleType Name
Two Wheels Bicycle
Two Wheels Scooter
Two Wheels Motorcycle
Four Wheels Sedan
Four Wheels SUV
Four Wheels Pickup
Four Wheels Minivan
The result I'm looking for would be
Table2
Vehicle Type
Two Wheels Bicycle, Scooter, Motorcycle
Four Wheels Sedan, SUV, Pickup, Minivan
SQL Server 2008 :: BCP XML File Format Error Invalid Ordinal For Field 2
Oct 1, 2010When creating xml fileformat its throwing me error "invalid ordinal".
When created non-xml file format, no error, and was also able to load data file into sql table. Not sure why bcp (Version: 10.50.1600.1) is not able to create xml file format.
C:>BCP "MyGDB.dbo.Items_Import" format nul -f"C:AnkitTempBCPItemsMaster.xml" -x -w -T -S"(Local)"
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid ordinal for field 2 in xml format file.
Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
Item Numbervarcharno18 noyesnoSQL_Latin1_General_CP1_CI_AS
Description1nvarcharno80 yes(n/a)(n/a)SQL_Latin1_General_CP1_CI_AS
Description2nvarcharno80 yes(n/a)(n/a)SQL_Latin1_General_CP1_CI_AS
UMvarcharno3 yesyesyesSQL_Latin1_General_CP1_CI_AS
SQL Server 2008 :: Long Text Field - Failed To Locate Ending Boundary Of A Sentence
Apr 23, 2015I have an very long ntext field, made up of many sentences that I append a full stop to every one, I also strip out any line breaks within the text. However I get this error, when I look it up it comes up with "Failed to locate the ending boundary of a sentence."
View 0 Replies View RelatedExtract Data From Middle Of String In SQL Server
Jan 11, 2008I am trying to write a query in sql query analyzer that will extract a date that appears after the first comma in the string. An example of the data is below:
DAVE,12/31/03,17:18,PERIODENDP
AS,01/16/06,16:51,CUSTOMERS_VW
EM,11/09/07,15:08,ORDER_ENTRY_
I want to select the 8 characters after the first comma in the field, and then convert it to a date format.
I am a novice, so any help with the correct function and syntax would be greatly appreciated.
thanks,
Dave