parse into two columns - 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 2005 Forums

Transact-SQL (2005)

parse into two columns

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2011-01-07 : 10:26:40

have the following varchar(30) column with data:

I want to output the column into two columns (account,security) basically taking everything before with as the security, and everything after with as numberofaccounts

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-07 : 11:15:08

I looked in BOL for String Functions and found a bunch of them. Here are some applications of those function:

DECLARE @T TABLE (val VARCHAR(30))

INSERT @T VALUES
('00758A9A1 with 1'), 
('01168A9A3 with 1'), 
('01853Z9C4 with 1'),
('01853Z9D2 with 1'), 
('01861A9A3 with 1'), 
('035465SP9 with 1'), 
('03852A9A0 with 1')


-- Only works for fixed width Account and  Security values
SELECT 
    LEFT(val, 9) AS Account, 
    RIGHT(val, 1) AS NumOfAccounts
FROM @T


SELECT 
    LEFT(val, CHARINDEX(' with ', val)) AS Account, 
    RIGHT(val, LEN(val) - CHARINDEX(' with ', val) - 5) AS NumOfAccounts
FROM @T


SELECT 
    SUBSTRING(val, 1, CHARINDEX(' with ', val)) AS Account,
    SUBSTRING(val, CHARINDEX(' with ', val) + 6, LEN(val) - CHARINDEX(' with ', val) - 5) AS NumOfAccounts
FROM @T

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-07 : 11:16:57

Is the Account always 9 characters followed by the "with"?

Jim

Everyday I learn something that somebody else already knew

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2011-01-07 : 11:26:56

Sorry, should of been more clear, the first half is the security, which is variable lenght, and the piece after the with will be variable as well

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-07 : 11:33:37

Then Lamprey has good suggestions (Books On Line is your friend!), you can also use PARSENAME if the data is always in the above "XXXXX WITH YYYYY" format.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources