splitting column into three

Question

splitting column into three

NEAL-464478

SSC-Addicted

Points: 461
More actions
July 1, 2010 at 3:54 pm

#222789

Is it possible to strip out DESTINATION field into three one COL should show all LCL, NDC and SCF second one local, NEW JERSEY NJ, SPFLD MA,PITTSBURGH PA, etc and the
third COL should show 001, 055, 151, 192, etc
Stream Destination Trays Pieces
========================================================================================================================
AUTO LCL LOCAL ENTRY 144 1,000,000
AUTO NDC NEW JERSEY NJ 001 49 16,332
AUTO NDC SPFLD MA 055 48 14,688
AUTO NDC PITTSBURGH PA 151 25 7,233
AUTO NDC PHILA PA 192 36 10,997
AUTO NDC WASHINGTON DC 204 34 10,636
AUTO NDC GREENSBORO NC 270 31 8,753
AUTO NDC ATLANTA GA 311 23 6,859
AUTO NDC JAXVILLE FL 320 44 13,232
AUTO NDC MEMPHIS TN 389 21 4,994
AUTO NDC CINCINN OH 459 35 9,647
AUTO NDC DETROIT MI 483 30 8,694
AUTO SCF MPLS/STP MN 552 22 5,953
AUTO NDC CHICAGO IL 608 30 9,669
AUTO NDC DALLAS TX 751 33 9,688
AUTO NDC DENVER CO 800 18 5,026
AUTO SCF LOS ANGELS CA 909 40 13,350
AUTO NDC SAN FRAN CA 948 37 11,326
AUTO NDC SEATTLE WA 980 31 9,041
========================================================================================================================
Totals: 587 176,118

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

NEAL-464478 (7/1/2010)
Is it possible to strip out DESTINATION field into three

Yes it is. However, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 2

As Wayne points out, table definitions and some sample data insert statements would be handy, but I came up with this quick demo which hopefully contains the seeds of what you might need:

DECLARE @test-2 VARCHAR(50)

SET @test-2 = 'NDC NEW JERSEY NJ 001'

SELECT LEFT(@test, 3) -- "NDC",

LEFT(RIGHT(@test, LEN(@test) - 4),LEN(@test) - 8) -- "NEW JERSEY NJ",

RIGHT(@test, 3) -- "001"

Of course, that assumes the first bit is always 3 characters followed by a space and the last bit is always a space followed by 3 characters, so if that's not the case you may need to make some modifications.

Duncan