splitting one column in 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

General SQL Server Forums

New to SQL Server Programming

splitting one column in multiple columns

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2011-10-12 : 14:14:29

Hi All,

I need to split one column to multiple columns in MS sql. I have a column 'TRX 66' delimited by spaces. I want to put TRX in one column and 66 in another column. I only have two values seperated by spaces, but spaces can be more or less so in other words it can be 'TRX 66'.

Sombodey told me the PL/sql solution for that, but is there any MS sql solution for the above. Below is the PL/sql solution

select TRIM (SUBSTR (TRIM ('TTR 102'),
1,
INSTR (TRIM ('TTR 102'),
' ',
1,
1)))
AS class_type,
TRIM (SUBSTR (TRIM ('DDG 102'),
INSTR (TRIM ('TTR 102'),
' ',
-1,
1)
+ 1))
FROM DUAL;

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 14:24:05

[code]
SELECT LEFT(YourCol + ' ',CHARINDEX(' ',YourCol + ' ')-1) AS FirstValue,
LTRIM(RTRIM(SUBSTRING(YourCol + ' ',CHARINDEX(' ',YourCol + ' ')+1,LEN(YourCol)))) AS SecondValue
FROM Table
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali5
Posting Yak Master

121 Posts

Posted - 2011-10-13 : 11:27:42

Thanks Visakh!! This solved my problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 12:16:45

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources