SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Case when like
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kwacz23
Starting Member

44 Posts

Posted - 09/10/2013 :  08:27:35  Show Profile  Reply with Quote
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

United Kingdom
10 Posts

Posted - 09/10/2013 :  08:34:12  Show Profile  Reply with Quote
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 #*
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
346 Posts

Posted - 09/10/2013 :  17:04:03  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000