question

darren 3 avatar image
darren 3 asked

Sql 2005 query on rows

How can u split alphabetsand numbers in a row and display the results in two separate rows

t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image
Scot Hauder answered
            
            
DECLARE @Temp Table(MixedRow varchar(4000))            
INSERT @Temp             
SELECT 'asfd34fv34325r432f4324325324gf432gfe34432edq' UNION ALL            
SELECT 'A01B0A1B010ABA10BAB1010A1B0A10BABABABA1BABA0'            
            
SELECT x.y FROM @temp t            
CROSS APPLY(SELECT            
            (SELECT CASE WHEN SUBSTRING(t.MixedRow,r,1) LIKE '[0-9]'             
                        THEN ''             
                        ELSE SUBSTRING(t.MixedRow,r,1)             
                        END             
            FROM             
                (SELECT r = ROW_NUMBER() OVER (ORDER BY c1.column_id)            
                 FROM master.sys.all_columns c1) i            
            WHERE r <= LEN(t.MixedRow)            
            FOR XML PATH(''))            
            UNION ALL SELECT             
            (SELECT CASE WHEN SUBSTRING(t.MixedRow,r,1) LIKE '[a-Z]'             
                        THEN ''             
                        ELSE SUBSTRING(t.MixedRow,r,1)             
                        END             
            FROM             
                (SELECT r = ROW_NUMBER() OVER (ORDER BY c1.column_id)            
                 FROM master.sys.all_columns c1) i            
            WHERE r <= LEN(t.MixedRow)            
            FOR XML PATH(''))            
)x(y)            
            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Squirrel 1 avatar image
Squirrel 1 answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.