Case when like - 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 2008 Forums

Transact-SQL (2008)

Case when like

Author

Topic

kwacz23
Starting Member

44 Posts

Posted - 2013-09-10 : 08:27:35

Hi

i got result from below query

user_logon order_number CountLinesPerOrder Month Year
aaa@pl.aa 1 1 1 2013
aaa@no.aa 1 1 2 2013

How I can extract @pl what means POLAND
@no means NORWAY

SELECT user_logon , order_number , COUNT(*) CountOfLinesPerOrder , MONTH(createDate) AS MDate , YEAR(createDate) YDate FROM SC_RequestData
GROUP BY user_logon ,order_number , MONTH(createDate) , YEAR(createDate)
HAVING order_number IS NOT NULL AND MONTH(createDate) >3 AND YEAR(createDate) = 2013
ORDER BY order_number

Cooper-5
Starting Member

10 Posts

Posted - 2013-09-10 : 08:34:12

assuming you only want the '@pl' section you could use charindex and substring to carve the user_logon into sections and use case statement for its 'FriendlyName'.

substring( user_logon , charindex('@'),3)

*# Nothing is truly idiot proof, because the world keeps producing a higher class of idiot #*

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-09-10 : 17:04:03

The code below will handle different lengths after the "@", but this code will get an error if the @ is missing:



SELECT user_logon , order_number, 
    SUBSTRING(user_logon, CHARINDEX('@', user_logon + '@') + 1, CHARINDEX('.', user_logon + '.', 
        CHARINDEX('@', user_logon + '@')) - CHARINDEX('@', user_logon + '@') - 1) AS country,
    COUNT(*) CountOfLinesPerOrder , MONTH(createDate) AS MDate , YEAR(createDate) YDate 
FROM SC_RequestData 
WHERE
    order_number IS NOT NULL AND
    createDate >= '20130401' AND
    createDate < '20140101'
GROUP BY order_number, user_logon , MONTH(createDate) , YEAR(createDate),
    SUBSTRING(user_logon, CHARINDEX('@', user_logon + '@') + 1, CHARINDEX('.', user_logon + '.', 
        CHARINDEX('@', user_logon + '@')) - CHARINDEX('@', user_logon + '@') - 1)
ORDER BY order_number

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources