Query to split Firstname Lastname into 2 fields

Good Afternoon Everyone,
I hope everyone is doing GREAT today. I've got a database where my
customers First and Last name are in ONE field (ContactName), and we are
upgrading to another SQL application that actually has (2) seperate fields,
FirstName and LastName. Does anyone know how I can run a query to seperate
the First and Last name and put it into two fields?
Right now this is how the new SQL database is:
FieldNames
FirstName LastName
Anthony Smith
I imported the whole contactname field into the FirstName field. So
Lastname is blank. I'd like to take the last name from the 1st field and
put that into the LastName field.
This is what I'd like to acheive:
FieldNames
FirstName LastName
Anthony Smith
Thanks!
Sincerely,
Anthony Smith
In God We Trust!
Are all the names names formatted the same? If so you can use CHARINDEX or
the LEFT & RIGHT fuctions like:
SELECT LEFT( @.name , CHARINDEX( ' ', @.name ) - 1 )
SELECT RIGHT( @.name , CHARINDEX( ' ', REVERSE( @.name ) ) - 1 )
If they are not formatted the same, you have some issues to ponder. What
should happen if there is a middle name or a middle initial? What if either
the firstname or the last name was missing? How would you address a part of
the name that has more than a single space in it? What about double
barrelled names?
Anith
|||Hi Anthony
The following example should point you in the right direction. A couple of
things to watch out for are people that have two first names ie. Mary Jane
Smith and that the formatting of the data is consistent ie. no double spacing
etc.
CREATE TABLE Names
(
FirstName VARCHAR(20),
LastName VARCHAR(20) NULL
)
INSERT Names SELECT 'Anthony Smith', NULL
INSERT Names SELECT 'Peter Ward', NULL
INSERT Names SELECT 'John Brown', NULL
INSERT Names SELECT 'Prince', NULL
INSERT Names SELECT 'Mary Jane Smith', NULL
UPDATENames
SETFirstName =
CASE
WHEN CHARINDEX(' ', FirstName) > 0 THEN SUBSTRING(FirstName, 1,
CHARINDEX(' ', FirstName) - 1)
ELSE FirstName
END,
LastName =
CASE WHEN CHARINDEX(' ', FirstName) > 0 THEN SUBSTRING(FirstName,
CHARINDEX(' ', FirstName) + 1, LEN(FirstName) - CHARINDEX(FirstName, ' '))
END
SELECT * FROM Names
Returns:
FirstName LastName
-- --
Anthony Smith
Peter Ward
John Brown
Prince NULL
Mary Jane Smith
HTH
- Peter Ward
www.wardyit.com
"Anthony Smith" wrote:

> Good Afternoon Everyone,
> I hope everyone is doing GREAT today. I've got a database where my
> customers First and Last name are in ONE field (ContactName), and we are
> upgrading to another SQL application that actually has (2) seperate fields,
> FirstName and LastName. Does anyone know how I can run a query to seperate
> the First and Last name and put it into two fields?
> Right now this is how the new SQL database is:
> FieldNames
> FirstName LastName
> Anthony Smith
> I imported the whole contactname field into the FirstName field. So
> Lastname is blank. I'd like to take the last name from the 1st field and
> put that into the LastName field.
> This is what I'd like to acheive:
> FieldNames
> FirstName LastName
> Anthony Smith
>
> Thanks!
> Sincerely,
> Anthony Smith
> In God We Trust!
>
>
|||> John Steve St.Smith deWaal III
:-))
create table #t ( [Name] varchar(40))
insert into #t ([Name]) values ('Smith,John E')
insert into #t ([Name]) values ('Smith,Bill')
insert into #t ([Name]) values ('Smith,Adam F')
insert into #t ([Name]) values ('St,Smith deWaal III')
--go
select LastName, FirstName, MiddleName
from (
select
Name,
substring(Name,1,Comma-1) LastName,
substring(Name,Comma+1,Spce-Comma-1) FirstName,
nullif(substring(Name,Spce+1,40),'') MiddleName
from (
select
Name,
charindex(',',Name) Comma,
charindex(' ',Name+space(1),charindex(',',Name)) Spce
from #t
) D
) SplitNames
drop table #t
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u0UwP5aVHHA.192@.TK2MSFTNGP04.phx.gbl...
> ... and be prepared to manually scrub names such as:
> John Steve St.Smith deWaal III
> I haven't used such, but there are tools out these for this particular
> purpose. Depending on how many names you have and the complexity of the
> names, such a tool might be cheaper in the end.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anthony Smith" <anthony@.peconet.com> wrote in message
> news:%23mepx5UVHHA.3948@.TK2MSFTNGP05.phx.gbl...
>
|||Thank you everyone for the prompts replies. I think most of them are
formatted the same but there may be a few that aren't. If it takes care of
the bulk of the customers that'll be fine, we can manually change the rest.
Have a blessed day everyone!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uj1IsLVVHHA.4828@.TK2MSFTNGP05.phx.gbl...
> Are all the names names formatted the same? If so you can use CHARINDEX or
> the LEFT & RIGHT fuctions like:
> SELECT LEFT( @.name , CHARINDEX( ' ', @.name ) - 1 )
> SELECT RIGHT( @.name , CHARINDEX( ' ', REVERSE( @.name ) ) - 1 )
> If they are not formatted the same, you have some issues to ponder. What
> should happen if there is a middle name or a middle initial? What if
> either the firstname or the last name was missing? How would you address a
> part of the name that has more than a single space in it? What about
> double barrelled names?
> --
> Anith
>

query to split a database column ?

How can i write a query to split a database column and shows 2 new columns. In my database column

I have 2 mixing items and need to split out to 2 columns. Normally I have to write a query and change parameter

and run another query.

For example a database column with average number and range number.

Thanks

Daniel

Can you post some DDL, sample data and expected results?

AMB

|||

Hai,

Can you try the below query, and let me know that, it relates to your requirement or not:

DECLARE @.Columns varchar(1000)

SET @.Columns = ''

-- Create a temporary table.

CREATE TABLE #TempTable(Items varchar(50))

INSERT INTO #TempTable(Items) VALUES('A')

INSERT INTO #TempTable(Items) VALUES('A')

INSERT INTO #TempTable(Items) VALUES('A')

INSERT INTO #TempTable(Items) VALUES('B')

INSERT INTO #TempTable(Items) VALUES('B')

INSERT INTO #TempTable(Items) VALUES('B')

INSERT INTO #TempTable(Items) VALUES('C')

INSERT INTO #TempTable(Items) VALUES('C')

INSERT INTO #TempTable(Items) VALUES('D')

INSERT INTO #TempTable(Items) VALUES('D')

-- Before

SELECT * FROM #TempTable

-- Make a column list

SELECT

@.Columns = @.Columns + '[' + Items + '], '

FROM #TempTable

GROUP BY Items

-- Check the column values exits or not.

IF ( @.Columns IS NOT NULL ) AND ( @.Columns <> '' )

BEGIN

DECLARE @.Query nvarchar(1000)

SELECT @.Columns = SUBSTRING(@.Columns,1, LEN(@.Columns)-1)

SELECT @.Query = '

SELECT

*

FROM

(

SELECT

Items

FROM #TempTable

) AS Dummy

PIVOT

(

MAX(Items)

FOR Items IN (' + @.Columns + ')

)AS PvtTable'

EXEC(@.Query)

END

-- Drop the temporary table.

DROP TABLE #TempTable

Please clarify If I did any wrong.

Regards,

Kiran.Y

|||

Perhaps something like:

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyGroup int,
MyValue decimal(10,2)
)

INSERT INTO @.MyTable VALUES ( 1, 25 )
INSERT INTO @.MyTable VALUES ( 2, 5 )
INSERT INTO @.MyTable VALUES ( 1, 10 )
INSERT INTO @.MyTable VALUES ( 1, 15 )
INSERT INTO @.MyTable VALUES ( 1, 4 )
INSERT INTO @.MyTable VALUES ( 2, 6 )
INSERT INTO @.MyTable VALUES ( 2, 11 )
INSERT INTO @.MyTable VALUES ( 2, 0 )
INSERT INTO @.MyTable VALUES ( 1, 12 )

SELECT
Average = cast( avg( MyValue ) AS decimal(10,2)),
Range = ( cast( min( MyValue ) AS varchar(10)) + '-' +
cast( max( MyValue ) AS varchar(10)))
FROM @.MyTable
GROUP BY MyGroup

Average Range
13.20 4.00-25.00
5.50 0.00-11.00

|||

Hi Kiran

Thanks for answering my email. To clarify this below are my tables and columns and my query

Table: Item Stat_label Stat_value

column: Pack ID Stat_label_ID Stat_value_ID

Pack_Num Label ( has 2 rows Value

Ave and Range)

My query to list Pack_Num, Ave and it's value

SELECT Item.Pack_Num, Stat_label.Label, Stat_value.Value

FROM Item, Stat_label, Stat_value

WHERE Item.packID=Stat_label.Stat_label_ID AND

Stat_label.Stat_lavel_ID=Stat_value.Stat_value_ID

AND Stat_label.Label= Ave

My question: I want a query to list Pack_Num, Ave, Range and value

How can I do it?

That's mean this query need to split the Stat_label and list another

column name"Range".

Thanks
Daniel

|||

If you are using SQL 2005, look into the PIVOT function.

If you are using SQL 2000, explore using CASE.

Maybe these articles will help:

Pivot Tables -A simple way to perform crosstab operations
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1131829,00.html

Pivot Tables - How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574

Pivot Tables -Dynamic Cross-Tabs
http://www.sqlteam.com/item.asp?ItemID=2955

Pivot Tables - Crosstab Pivot-table Workbench
http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/

|||

Thanks all

I can not use "insert" because my account for this is read only and I avoid to list everything in a column and and use Excel pivot to summary.

Daniel

|||

Daniel,

If you would carefully examine the code provided, you will see that the INSERT statements are only building a sample table so that we could demonstrate a query suggestion.

You didn't bother to provide the table DDL, or sample data, so we have to waste our time creating sample data for you. and apparently, you can't read and understand example code.

|||

This may be closer to what you are hoping to find:

SELECT

i.Pack_Num,

sl.Stat_Label,
Average = cast( avg( sv.Stat_Value ) AS decimal(10,2)),
Range = ( cast( min( sv.Stat_Value ) AS varchar(10)) + '-' +
cast( max( sv.Stat_Value ) AS varchar(10)))
FROM Item i

JOIN Stat_Label sl

ON i.Pack_ID = sl.Stat_Label_ID

JOIN Stat_Value sv

ON sl.Stat_Label_ID = sv.Stat_Value_ID

WHERE sl.Label = 'Ave'
GROUP BY

i.Pack_Num,

sl.Stat_Label

|||

Thanks Anrnie but It is not working

Error at Average= cast......

Error at Range= (cast......

My Average and Range are decimal, no need cast

Do I have to declare a temp table?

Daniel

|||

Actually, it appears that the Stat_Value is most most likely a varchar().

Before we can help you any further, please post the table DDL and some sample data in the form of INSERT statements. Please refer to this link for help in preparing your material.

|||

Can SQL query create a new column or not?. DO NOT want to make a temp table.

Thanks

Daniel

|||

Can TSQL create a new column at the output?

If not I need 2 select statement but how to joint them? Can not use EXCEPT in TSQL? Tried to use UNION but

the results in one column.

It's complicated with creating a temp table since I do not know how to insert to temp table from database.

Thanks

Daniel

|||Please supply the requested information. (See my previous post.)

sql

Query to concatenate results from multiple rows

I have a database where comments are stored in a separate table where the comment is split into max 80 char lengths and stored in separate rows.

eg.

RecordID Comment
001 This is a comment and the nex
001 t bit of the comment appears o
001 n the next line.
002 This is the start of the next com
002 ment.

I need a SQL query that will put the text back together again.

Many thanks
MUHow do you determine which order the segments should be assembled? Can they be put together in random order, or is there a definite sequence?

Do you want a solution that is simple, but SQL dialect specific, or do you want a generic solution that will work with most/all SQL dialects?

Do you want a solution for a single ID, or does it need to be able to work for the entire table in a single operation?

-PatP|||Pat,
Thanks for the response.

There is a LineNum field in the table to order the comments by.

The solution only needs to work with SQLServer.

Ideally I am looking for a solution that produces an entire set of rows showing details from a master table with the comment appearing from this table as a single field with the RecordID being used as the join field.

MarkU|||Ok, if you need to process multiple rows in a single set operation (ie SELECT statement), the best answer I've got is:CREATE TABLE #phrog (
recordId CHAR(3)
, comment VARCHAR(80)
, lineNum INT)

INSERT INTO #phrog (recordID, comment, lineNum)
SELECT '001', 'This is a comment and the nex', 1
UNION ALL SELECT '001', 't bit of the comment appears o', 2
UNION ALL SELECT '001', 'n the next line.', 3
UNION ALL SELECT '002', 'This is the start of the next com', 1
UNION ALL SELECT '002', 'ment.', 2

SELECT a.recordID, a.comment + Coalesce(b.comment, '') + Coalesce(c.comment, '')
FROM #phrog AS a
LEFT JOIN #phrog AS b
ON (b.recordID = a.recordID
AND b.lineNum = (SELECT Min(z1.lineNum)
FROM #phrog AS z1
WHERE z1.recordID = a.recordID
AND a.lineNum < z1.lineNum))
LEFT JOIN #phrog AS c
ON (c.recordID = a.recordID
AND c.lineNum = (SELECT Min(z1.lineNum)
FROM #phrog AS z1
WHERE z1.recordID = a.recordID
AND b.lineNum < z1.lineNum))
WHERE a.lineNum = (SELECT Min(z0.lineNum)
FROM #phrog AS z0
WHERE z0.recordID = a.recordID)

DROP TABLE #phrogBe forewarned that this code raises the kludge factor of the universe significantly, but it does work.

-PatP|||Many thanks for your help - I will check this out.

What I don't quite understand is that since I don't know upfront how many lines of comments there may be or what is in them, how can I do the UNION statements?

I was hoping that there would be some form of the UNION statement where I could say UNION ALL comment WHERE recordId = n (or similar).

MarkU|||On second thought, lets apply a very "Oracle-ish" solution. You could also use:CREATE TABLE dbo.phrog (
recordId CHAR(3)
, comment VARCHAR(80)
, lineNum INT)

INSERT INTO dbo.phrog (recordID, comment, lineNum)
SELECT '001', 'This is a comment and the nex', 1
UNION ALL SELECT '001', 't bit of the comment appears o', 2
UNION ALL SELECT '001', 'n the next line.', 3
UNION ALL SELECT '002', 'This is the start of the next com', 1
UNION ALL SELECT '002', 'ment.', 2
GO

CREATE FUNCTION dbo.phrogComment(@.recordID CHAR(3))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE
@.c VARCHAR(8000)
, @.r VARCHAR(8000)

SET @.r = ''

DECLARE z CURSOR FOR SELECT
comment
FROM dbo.phrog
WHERE recordID = @.recordID
ORDER BY lineNum

OPEN z
FETCH z INTO @.c

WHILE 0 = @.@.fetch_status
BEGIN
SET @.r = @.r + @.c
FETCH z INTO @.c
END

CLOSE z
DEALLOCATE z

RETURN @.r
END
GO

SELECT a.recordID, dbo.PhrogComment(a.recordID)
FROM dbo.phrog AS a
GROUP BY a.recordID

DROP FUNCTION dbo.phrogComment
DROP TABLE dbo.phrogThis will grieviously disturb the relational purist (me included), but it will get the job done quickly and simply.

-PatP|||I tried the second bit of code on my own tables, and it almost works perfectly. The problem I have is that the concatenated field being returned is being truncated at 256 total characters/spaces, yet I need it to be larger.

I tried to use a CAST on the PhrogComment(a.ID), as well as changing the VARCHAR sizes for @.c and @.r and the RETURNS value, all to no avail.

Any suggestions on how I could tweak the code to make the result "larger"?

Thanks,

Mark|||'taint the SQL code what's cuttin' ya off. It's the client.

In Query Analyzer:

1) Press shift-control-o to bring up the Options window.
2) Click the results tab.
3) At the right edge, near the middle, type in whatever column width seems kozy but not extravagant.
4) Re-run your query for optimum viewing pleasure!

Sorry if I'm a bit punchy... Things could charitably be described as "interesting" today.

-PatP|||Praise God! I've been losing my mind for the last 24 hours (it's been - how did you say it? - "interesting" :-)

Thanks so much. I should've known to blame it on SQL Query Analyzer - I've had some queries not work (i.e., a query will return 0 rows and throw no errors) in the Analyzer yet the same query works (return the expected results) if cut and pasted into and then run as a stored procedure - go figure.

Then again, I'm an econ major so the problem is probably behind the keyboard...

Mark|||Are you just wanting to do this for one message at a time in your procedure or are you wanting to return several messages.