Another Parsing Problem - 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 2012 Forums

Transact-SQL (2012)

Another Parsing Problem

Author

Topic

kennyb
Starting Member

5 Posts

Posted - 2013-08-27 : 22:38:24

I have an inherited db with a column called pedigree. Here are some examples of the data:
97
110
98?
115*
*

Basically, it is a numerical rating with an optional qualifier appended. The number will be either 2 or 3 digits (or missing altogether.) The qualifier is meant to designate a limited sample size.

Of course, I would like to split this into two columns.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-28 : 02:35:47

SELECT Pedigree AS Original, REPLACE(Pedigree, '*', '') AS Pedigree, CASE WHEN RIGHT(Pedigree, 1) = '*' THEN '*' ELSE '' END AS Qualifier
FROM dbo.Table1

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

kennyb
Starting Member

5 Posts

Posted - 2013-08-29 : 21:16:01

So close. I needed to point out that the qualifier can also be a question mark "?", as in the "98?".

KB

kennyb
Starting Member

5 Posts

Posted - 2013-08-31 : 21:24:12

Thanks, all.

I finally got it:

Case
When Right([b].[Pedigree], 1) = '*'
Then Left ([b].[Pedigree], CharIndex('*', [b].[Pedigree])-1)
When Right([b].[Pedigree], 1) = '?'
Then Left ([b].[Pedigree], CharIndex('?', [b].[Pedigree])-1)
Else [b].[Pedigree]
End as DirtPedNum,

CASE
WHEN RIGHT([b].[Pedigree], 1) = '*'
THEN '*'
WHEN RIGHT([b].[Pedigree], 1) = '?'
THEN '?'
ELSE ''
END AS DirtQ,

KB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-01 : 02:41:36

[code]CASE
WHEN RIGHT(b.Pedigree, 1) IN ('*', '?') THEN LEFT(b.Pedigree, LEN(b.Pedigree) - 1)
ELSE b.Pedigree
END AS DirtPedNum,[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources