Split single column into multiple columns

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 2005 Forums

Transact-SQL (2005)

Split single column into multiple columns

Author

Topic

mmccardle
Starting Member

43 Posts

Posted - 2011-01-14 : 14:57:06

I have a single column that contains a file name, this filename holds data that is delimited by an underscore. I need to split this filename up so that the data resides in its own column, for comparative purposes.

Current List:


Filename
p_22124_R_01.jpg
p_22124_R_02.jpg
p_22138_H_01.jpg
p_22138_H_02.jpg
p_22150_B_01.jpg
p_22150_B_02.jpg
p_22151-1_A_01.jpg
p_22151-1_A_02.jpg
p_22151-1_A_03.jpg

List I need:


FileName            Item        Revision        Number
p_22124_R_01.jpg    22124        R              01
p_22124_R_02.jpg    22124        R              02
p_22138_H_01.jpg    22138        H              01
p_22138_H_02.jpg    22138        H              02
p_22150_B_01.jpg    22150        B              01
p_22150_B_02.jpg    22150        B              02
p_22151-1_AA_01.jpg 22151-1      AA             01 
p_22151-1_AA_02.jpg 22151-1      AA             02
p_22151-1_AA_03.jpg 22151-1      AA             03

I tried a google search but couldn't find examples quite like what I need. Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-14 : 16:19:36

Here is one way:

DECLARE @T TABLE (Filename VARCHAR(100))

INSERT @T (Filename)
VALUES
('p_22124_R_01.jpg'),
('p_22124_R_02.jpg'),
('p_22138_H_01.jpg'),
('p_22138_H_02.jpg'),
('p_22150_B_01.jpg'),
('p_22150_B_02.jpg'),
('p_22151-1_A_01.jpg'),
('p_22151-1_A_02.jpg'),
('p_22151-1_A_03.jpg')


SELECT
    FName
    , PARSENAME(FName, 3) AS Item
    , PARSENAME(FName, 2) AS Revision
    , PARSENAME(FName, 1) AS Number
FROM
(   
   SELECT REPLACE(REPLACE(REPLACE(Filename, '_', '.'), '.jpg', ''), 'p.', '') AS FName FROM @T
) AS T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources