Splitting Out Names From One Field Delimted By Comma And A Space

Apr 28, 2004

have 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

View 3 Replies


ADVERTISEMENT

Inserting Into ONE Column A Comma Delimted String??

Dec 1, 1999

I have a string of comma delimited values such as the following:

1,2,3,4,5

I have a table with ONE column only. How can I do an insert statement which will insert each of these values?

If I type the following:
"INSERT INTO tabletest SELECT 1,2,3,4,5"

I get this error:
"Insert error: column name or number of supplied values does not match table definition. "

This makes sense because I do not have 5 columns I only have 1. But how can I get around this?


Any help is most appreciated! Thanks in advance...mary

View 1 Replies View Related

Createing SQL Data Base Record For Comma Delimted Records

Sep 15, 2006

I am a new user to SQL an I need to create a data base record from a comma delimted file (.CVS). The CVS file has up to ten fields all alpha/numeric. I must create this data base file using a stored procedure! The data base records will be displayed on a grid screen but this grid view will not be used to update the original CVS file.

View 6 Replies View Related

Splitting A Comma Delimited Column

Feb 12, 2008



Hi

I wondered if anyone could help me. I have a table that holds an ID in the first column then a list of values split by commas in the 2nd column i.e.:

IDColumn: 1
2ndCoumn: 1stvalue, 2ndvalue, 3rdvalue, 4thvalue

I am trying to return as a dataset of this that shows the ID as column 1 then each value in the 2nd column as individual columns if I use SELECT LEFT('2ndColumn,PATINDEX(',',2ndColum)-1) I can return the first value as a column but then can't return any further values individually after the first column, I am just learning the new functionality in SSIS so not sure whether this would be my answer as apposed to T-SQL, if anyone has any advice on this it would be greatly appreciated?

Thanks in advance

Caralyn

View 7 Replies View Related

Splitting A Comma Delimited String

Oct 1, 2007

I have a string like say: '3:4:5:4,2:4:1,4:1:2:5:2'. Now I need to split the substrings delimited by commas. So my final output shall be

3:4:5:4
2:4:1
4:1:2:5:2


I did write a piece of code to achieve the same, but I feel its not so efficient. Can anyone suggest me a better way, if any? My code is as follows:

Declare @person as varchar(255), @cnt smallint,@loc smallint,@prevloc smallint, @str varchar(255)
Select @prevloc=0,@loc=1,@cnt=1,@person = '3:4:5:4,2:4:1,4:1:2:5:2'
While @loc != 0
begin
set @prevloc=(case when @loc = 1 then 0 else @loc end) +1
set @loc = charindex(',',@person,@loc+1)
Set @str = substring(@person,@prevloc,(Case when @loc = 0 then len(@person) - @prevloc + 1 else @loc - @prevloc end))
print 'String = ' + @Str
set @cnt=@cnt+1
end

RESULT
------
String = 3:4:5:4
String = 2:4:1
String = 4:1:2:5:2


Note: My actual purpose is to also sub split it again with ':' delimiter too. So looking for an efficient code.

View 4 Replies View Related

Help Required For Splitting Up String Variable Using Comma Separator

Jul 9, 2007

I need a help in SQL Server 2000.
I am having a string variable in the format like -- (1,23,445,5,12)
I need to take single value at a time (like 1 for 1st, 23 for 2nd and so on) from the variable and update the database accordingly. This is like a FOR loop.
Can anyone help me out in splitting the variable using the comma separator...

View 4 Replies View Related

Splitting A Comma Seperated Column Into Multiple Rows

Jan 18, 2006

Hi i want to create a table as follows :if exists (select * from dbo.sysobjects where id =object_id(N'[Indexes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [Indexes]GOCreate table Indexes(indexname Varchar(100), index_DescriptionVarchar(100), index_keys Varchar(100))GOINSERT INTO Indexes EXEC sp_helpindex 'SDM_Fact_Order_Detail'GOThis will give me a table (northwind)IX_Auto_SDM_Fact_FK_Shipped_Date nonclustered located onSAMIS_SDM_Index FK_Shipped_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Dateclustered located onSAMIS_SDM_Data1FK_Insert_Date, FK_Insert_TimeAs you see sp_helpindex will give me a comma seperated field. I wantto split the third column FK_Insert_Date, FK_Insert_Time into a extrarowLike this :IX_Auto_SDM_Fact_FK_Shipped_Date FK_Shipped_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date FK_Insert_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date FK_Insert_TimeCan anyone help me with this?ThanxHennie

View 1 Replies View Related

Splitting Space Delimited String Inline

May 20, 2008

So we have a field called forenames, and it needs to be split into fields forename_1, forename_2, forename_3, forename_4 (don't ask).

Ok, I've come up with this so far, which works, but is pretty nacky in my opinion. Has any one got a better way of achieving this?

SELECT forenames
, Replace(forenames, ' ', '.')
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 1)) As [f1]
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 2)) As [f2]
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 3)) As [f3]
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 4)) As [f4]
FROM ( SELECT 'John' As [forenames]
UNION SELECT 'John Paul'
UNION SELECT 'John Paul George'
UNION SELECT 'John Paul George Ringo'
) As [x]

Results

forenames (no column name) f1 f2 f3 f4
---------------------- ---------------------- ---- ---- ------ -----
John John John NULLNULL NULL
John Paul John.Paul John PaulNULL NULL
John Paul George John.Paul.George John PaulGeorgeNULL
John Paul George Ringo John.Paul.George.Ringo John PaulGeorgeRingo

View 7 Replies View Related

Splitting A Field Into Two

Jul 20, 2005

I 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 Related

Need Help Splitting Contents Of A Field In T-Sql

May 22, 2006

I 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

View 4 Replies View Related

Splitting A Text Field

Feb 1, 2001

hi,
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

View 3 Replies View Related

Problems Splitting Name Field

Sep 1, 2006

I 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)))

View 7 Replies View Related

SQL Syntax Trouble (Splitting A Field In Two)

Apr 12, 2007

I 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

View 5 Replies View Related

Splitting Name Field To FName And Last Name In Package

Apr 2, 2007

Hi



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

View 7 Replies View Related

SQL Server 2008 :: Splitting A Field In The Middle?

Mar 19, 2015

I have a field which looks like "LastName, FirstName (DOB: 01/01/1900)"

How do I get the "01/01/1900" between ":" and ")"

View 6 Replies View Related

Table Names And Field Names

Jan 21, 2004

I'm trying to do an update query that looks like this:

UPDATE

PAEMPLOYEE

SET PAEMPLOYEE.LOCAT_CODE = EMPLOYEE.PROCESS_LEVEL


FROM

PAEMPLOYEE A

JOIN EMPLOYEE B ON A.EMPLOYEE = B.EMPLOYEE

It's erroring out on the Employee prefix B.EMPLOYEE saying:

..."does not match with a table name or alias name used in the query"


Is it wrong or will it cause problems to have a field name the same as the table name?

View 5 Replies View Related

Splitting One Data Field With Hyphens To Separate Records

Feb 16, 2004

I 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

View 2 Replies View Related

Help - How To Select From A Comma Delimited Field ?

Nov 2, 2003

Dear SQL,

Lets say I have a field: User_Names

and it can have more than one name
example: "Yovav,John,Shon"

How can I select all the records that has "Yovav" in them ?

I try some like this:
SELECT * FROM User_Table WHERE User_Names IN ('Yovav')

but it only works if the field User_Names only has "Yovav" in it with no extras...


is there some SQL function to tell if string is found in a field ?

Any hope 4 me ?

View 2 Replies View Related

Split Up Comma-delimited Field

Jun 7, 2002

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 9 Replies View Related

Insert Number With Comma Into SQL Database 'int' Field

Feb 8, 2006

How can I allow users to input numbers with commas into a database field with an 'int' datatype without getting this error, 'Input string was not in a correct format'?

View 3 Replies View Related

Select Many Records Into 1 Comma Seperated Field.

Oct 30, 2006

I want to pull out some information for documenting my user accounts.I want to be able to show all of the users and what companies those users have access to.

The table which stores the company information lists it in multiple rows, so there will be many results for each user
user -- companyid
johnd -- 2
johnd -- 5
johnd -- 1

I want to select this into one record so it will show up like this
user -- companies
johnd -- 2,5,1

The only way I know how to do this would be with a sub-query that uses a cursor to loop through & dump it into one string value, and then output the string value to the field. This seems extremely inefficient for such a seemingly simple task.

Can somebody help me out here?
thanks

View 3 Replies View Related

Comma Seperated Field To Arrays For Each Record?

May 12, 2008

Hello,

My SQL knowledge is limited so if I get stuff wrong then correct me... but I can imagine this task will be quite testing...

I am working on a system that logs ([Audit] table) the changes to fields on some tables using a Trigger on UPDATE. I need to produce a 'quick' report that returns the date when the tables overallStatus field was set to 1.

In the [Audit] table I can find all the field changes for the record in question using this SQL...

select *
from audit
where rowid = 1309606
order by auditID asc


My problem is filtering this data. The fields I need are formatted as below, see records returned, in ASC order...

audit.AuditID = 2652583
audit.OperationTime = 2008-04-24 15:12:07.740
audit.ColumnDetail = 'estimatedProductionPriceactualProductionCost'
audit.EditDetail = '0.00000.0000'


audit.AuditID = 2658460
audit.OperationTime = 2008-04-25 10:51:47.930
audit.ColumnDetail = 'overallStatusInsertionStatus'
audit.EditDetail = '05'



audit.AuditID = 2665723
audit.OperationTime = 2008-04-25 22:06:50.200
audit.ColumnDetail = 'overallStatusdespatchDateInsertionStatus'
audit.EditDetail = '1Â 3'



audit.AuditID = 2711092
audit.OperationTime = 2008-04-30 17:22:12.593
audit.ColumnDetail = 'overallStatusInsertionStatus'
audit.EditDetail = '34'



audit.AuditID = 2713217
audit.OperationTime = 2008-04-30 20:46:34.817
audit.ColumnDetail = 'clientOrderNumber'
audit.EditDetail = 'PAT12P7640'


The funny character, , is ASCII 127.


So need to find when 1309606's overallStatus was changed to 1. Manually looking at the data, I can see overallStatus was modified in 3 of the above 5 [Audit] records. It started life as 0, then went to 1 and then to 3. I'm aware that I need to look at the previous Audits date for when it was changed to the value I'm looking for... So it was changed to 1 on 2008-04-25 10:51:47.930.


What is the best way to approach this problem? I'm hoping to use T-SQL only and not have to use an external scripting language, unless I can embed vbscript or jscript inside a T-SQL function and then use arrays, etc?

Somehow I need to convert the ASCII(127) seperated list of fieldname into an array or list, find the index of the fieldname 'overallStatus' and then lookup that value in the datafield.


Hope that makes sense and any help would be great!


Cheers,
Nick

View 6 Replies View Related

Field Contains Multiple Values Seperated By Comma

May 3, 2006

Hi All

I'm really new to SQL and would appreciate a solution to the following problem.

I have a table that contains 1 record. in that record there is a column called CODES which contains multiple values that are seperated by a comma.

C200,C201,C202,C204,C200,C203,C200,C202,C203,C205,C200,C202,C203,C204,C205,C206,C200,C201,C202

I need a solution to select all values from CODES that are distinct so I end up with the following.

C200,C201,C202,C203,C204,C205,C206

Any help would be really appreciated. Please bear in mind I'm a complet novice.

View 4 Replies View Related

How Do You Parse A Single Field List Of Values Separated By Comma?

Jan 3, 2008


IE:
ID ContactID

1 4, 5, 6, 8
2 3,4,6

Someone coded their database like this. It is a SQL server table with these two fields.
How do I use SSIS to parse out that single field?

View 5 Replies View Related

SQL Server 2008 :: Turn Field Values Into Comma Separated List

Aug 11, 2015

I 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

View 2 Replies View Related

How To Insert Results Of A SQL Query Into The Field Of Another Table, As Comma Seperated Values.

Mar 7, 2008



I am currently working on an application that requires, insertion of the results of a SQL Query in to the field of another table, in the form of a comma separated values.

For example, to explain things in detail:

create table dbo.phone_details

(country varchar(20),
state varchar(30),
list_of_toll_free_numbers text)


insert into dbo,phone_details
values
( 'USA', 'CA', 'select Phone from phone_table where substring(phone, 1, 3) in ('800', '866', '877', '888')' )

The final output I desire is:

country state list_of_toll_free_numbers

---------- ------- -----------------------------------------
USA CA 8009877654, 8665764398, 8776543219

View 8 Replies View Related

Extracting XML From An SLQ Table Column Then Getting Delimted Vales From Within That XML...

Dec 6, 2007

Hi all.

This is my first SSIS project it's also an utter mare.

I'm a C# developer with reasonable SQL knowledge but I haven't used SSIS projects in BIS before.

What we have (for some insane reason) is an SQL table like so:

Column A |Column B |Column X |

Where Column X contains XML like this:
<Column C></Column C><Column D></Column D><Column ArrgWTF></Column ArrgWTF>

Where <Column ArrgWTF> contains two '-' delimited values.

What I need is some guidence (or just being told if its possible or not) to take this data and arange it like so (see bellow) in a new SQL table:

Column A |Column B |Column C | Column D |Column ArrgWTF1 |Column ArrgWTF2 |

Where A&D are from the original SQL table, C & D are pulled from the XML and ArrgWTF1 & 2 are sourced from the '-' delimited vales within the XML.

Many, many, thanks in advance.

View 6 Replies View Related

SQL Server 2012 :: Expand Comma Separated Values In Field In Select Statement

Jul 13, 2015

Consider the following data:

create table #test
(id int
,color varchar(20)
)
insert into #test
(id, color)
values
(1, 'blue'),(2, 'red'),(3,'green'),(4,'red,green')

if I wanted to run a query to select any records that had red in the color field, how would I do that? Not the one with only red, but a query that would give me both record number 2 and record number 4.

View 9 Replies View Related

Field Names From SQL Statement

Mar 9, 2004

Is there anyway to determine what the resulting Field Names are going to be from a SQL Statement?

For example:
SELECT TABLE1.FIELD1, TABLE1.FIELD2, TABLE1.FIELD3, TABLE2.FIELD1 AS ANOTHERNAME
FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.PK = TABLE2.FK

resulting field names:
FIELD1
FIELD2
FIELD3
ANOTHERNAME

Seems easy enough splitting all values before "FROM" by comma and doing some manipulation to remove table names and anything before the word "AS". However, it gets more difficult when you have complex CASE statements embedded in you query that may also contain commas.

Just a shot in the dark because I don't know if anyone has already done something like this before.

Thank you in advance,

Jeff

View 2 Replies View Related

Return Field Names

Jun 1, 2005

Howdy all,I'm wishing to write a stored proc to return only the field names from a table.  What I've tried gets the field names but also returns all of the data in each row.  I only want the field names.  Is this possible?Thanks!JP

View 7 Replies View Related

Changing Field Names

Jan 27, 2008

This is a followup to my last post. If a field name is changed in the database, what is the easiest way to determine what stored procedures and triggers that reference that field are now broken?

View 3 Replies View Related

SQL For Field Names From A Table

Oct 6, 2005

Hi,We have a database with some tables with (what I woulddenote as) 'referred field names'.Like this:DataTable1 with fields F1, F2, F3DataTable2 with fields F3, F4, F5DataTable3 with fields F1, F5, F2We also have a table with field namesFieldNameTable with fields FIELD, NAMEcontaining data like:FIELD NAME----------------F1 FieldName1F2 FieldName2F3 FieldName3F4 FieldName4F5 FieldName5Now, we need a way to query the data of these tables, butthe result of the query should show the 'referred field names'from the FieldNameTable.For example, querying DataTable3 should produce the outputFieldName1 FieldName5 FieldName2------------------------------------------... ... ...... ... ...Any idea how (and whether) this can be done with an SQL query?Thanks in advance for tips & tricks.Dirk Vdm

View 6 Replies View Related

SQL Server 2014 :: Split Out A Field Of Comma Separated Values Based On Unique Code In Same Row?

Oct 21, 2014

I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.

E.g

Unique Code Comma Separated Field

14587934 1,5,17,18,19,40,51,62,70

6998468 10,45,62,18,19

79585264 1,5,18

These needs to be in column format or held in an array to be used as conditional criteria.

Unique Code Comma Separated Value

79585264 1

79585264 5

79585264 18

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved