Name Separation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Name Separation

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-11-10 : 18:51:13

I have found an old access table that I would like to load into SQL. The name field has both the Last and First name connected with a "~" IE: Smith~Bob.

Any ideas on how to seperate the names so that they can be loaded into individual fields with the rest of the data. IE LName: Smith FName: Bob.

Thanks.

GC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-10 : 18:54:20

DECLARE @name nvarchar(255)

SET @name = 'Smith~Bob'

SELECT SUBSTRING(@name, 1, PATINDEX('%~%', @name) - 1) AS LastName, SUBSTRING(@name, PATINDEX('%~%', @name) + 1, DATALENGTH(@name)) AS FirstName

Tara

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-11-11 : 11:10:44

Tara,

As always thanks for the quick reply.

Regards.

GC

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-11-15 : 14:37:32

I have another old table where two sets of numberics that idenify a Station and Department are linked together by a '/'

IE: 0961/6227

I tried the format provided above and it does seperate the two groups, but places the same numbers as what was entered in the SET area. Here is the query.

DECLARE @CostCenter CHAR(9)
SET @CostCenter='0961/6227'
SELECT SUBSTRING(@CostCenter,1,PATINDEX('%/%', @CostCenter) - 1)AS'Station',
SUBSTRING(@CostCenter,PATINDEX('%/%', @CostCenter)+1,DATALENGTH(@CostCenter))AS'Branch',
[AutoTACurrentPayDaily].[EmployeeID],
[AutoTACurrentPayDaily].[CostCenter],
[AutoTACurrentPayDaily].[PayDate],
[AutoTACurrentPayDaily].[PayCode],
[AutoTACurrentPayDaily].[PayHours]
FROM [AutoTACurrentPayDaily]
WHERE [AutoTACurrentPayDaily].[CompanyCode]='AA'
AND [AutoTACurrentPayDaily].[PayCode]IN('SK', 'SKF', 'SKU', 'FML', 'NC', 'UA')
AND [AutoTACurrentPayDaily].[PayHours]>'0'
AND [AutoTACurrentPayDaily].[PayDate]<=[AutoTACurrentPayDaily].[PayPeriodEnd]
AND [AutoTACurrentPayDaily].[PayDate]>= '12/20/2002'
AND [AutoTACurrentPayDaily].[PayDate]<= '01/06/2003'
ORDER BY [AutoTACurrentPayDaily].[PayDate]ASC,
[AutoTACurrentPayDaily].[EmployeeID]ASC

Here is the response:
Sta BRA EMPID CostCenter Date PayCode Hours
0961 6227 00001234 0961/6027 2002-12-20 00:00:00.000 SKU 8
0961 6227 00005678 0346/4410 2002-12-20 00:00:00.000 SK 4
0961 6227 00009123 0600/4600 2002-12-20 00:00:00.000 SK 8
0961 6227 00004567 0090/4520 2002-12-20 00:00:00.000 SK 5
0961 6227 00008912 0090/4520 2002-12-20 00:00:00.000 SKU 3
0961 6227 00003456 0600/4600 2002-12-20 00:00:00.000 SK 8

I would like to see the results in the CostCenter column split into the STA and BRA columns.
Thanks.

GC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-15 : 14:41:15

In place of the variable, you need to use your column name. The variable is used to show you how it works.

Tara

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-11-15 : 16:35:33

Tara,

Got it!!! Thanks again.

GC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources