May 24, 2011 at 4:30 am
I have the following TSQL script -
SELECT dbo.DW_SALESORDERS_F.Reference AS [Soh.Reference]
,dbo.DW_USERS_D.UserShortName AS [Usr.UserShortName]
,dbo.DW_SALESORDERS_F.CreatedDate AS [Soh.CreatedDate]
,DATEPART(YYYY, dbo.DW_SALESORDERS_F.CreatedDate) AS [Soh.CreatedYear]
,DATEPART(WK, dbo.DW_SALESORDERS_F.CreatedDate) AS [Soh.CreatedWeek]
,dbo.DW_SALESORDERS_F.Note AS [Soh.Note]
,CASE WHEN Note LIKE '%%-%' THEN 'Contact Added'
ELSE 'No Valid Contact'
END AS [Cal.Contact]
,CASE WHEN LEFT (Note,1) = 'C' THEN 'TEST'
ELSE 'TEST1' END AS 'SQL'
,CASE WHEN dbo.DW_CUSTOMERS_D.ParentCustomerID = '55' THEN 'Hyde Housing'
WHEN dbo.DW_CUSTOMERS_D.ParentCustomerID = '23452' THEN 'Hyde In Touch'
ELSE 'Hyde Vale' END AS ParentClient
FROM dbo.DW_SALESORDERS_F
INNER JOIN
dbo.DW_USERS_D
ON dbo.DW_SALESORDERS_F.CreatedByID = dbo.DW_USERS_D.UserUID
INNER JOIN
dbo.DW_CUSTOMERS_D
ON dbo.DW_SALESORDERS_F.CustomerID = dbo.DW_CUSTOMERS_D.CustomerUID
WHERE (dbo.DW_CUSTOMERS_D.ParentCustomerID IN ('55','23452','24430' ))
AND (dbo.DW_SALESORDERS_F.CreatedDate >= CONVERT(DATETIME, '2011-04-01 00:00:00', 102))
AND (dbo.DW_SALESORDERS_F.CreatedDate <= CONVERT(DATETIME, '2012-03-31 00:00:00', 102))
AND (dbo.DW_SALESORDERS_F.SalesOrderCategory = N'RR1')
The issue I am having is with the following part of the SELECT statement
,CASE WHEN LEFT (Note,1) = 'C' THEN 'TEST'
ELSE 'TEST1' END AS 'SQL'
At present when the note field begins with C it comes back with TEST.
However I want to know when the third character is a - ? I have therefore changed the CASE statement to the following -
,CASE WHEN LEFT (Note,3) = '-' THEN 'TEST'
ELSE 'TEST1' END AS 'SQL'
but all my results come back as TEST1 when i can see that there are instances when a - is the third character.
Please advise.
Thanks
May 24, 2011 at 4:34 am
Change LEFT to SUBSTRING.
LEFT(expression, 3) returns the first 3 characters of the expression, not the third character.
Use SUBSTRING(expression, 3, 1) for the third character.
Hope this helps
Gianluca
-- Gianluca Sartori
May 24, 2011 at 4:40 am
Fantastic - Never come across the SUBSTRING expression.
Therefore if I wanted to say if the - was the third or forth character, how would you express that?
Thanks ever so much
May 24, 2011 at 6:03 am
You could use CHARINDEX(sought_char, expression, start_index):
WITH TestStrings (s) AS (
SELECT 'aaa-'
UNION ALL
SELECT 'bb-'
UNION ALL
SELECT 'ccccc-'
)
SELECT *
FROM TestStrings
WHERE CHARINDEX('-',s,3) IN (3,4)
This code returns 'aaa-' (fourth character) and 'bb-' (third character).
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply