Distinct values from SQL

Transact-SQL
21

Have a look at that and see what sorts of data you are getting (ie. which isn't "(Ordered as: ...)"

I may have misunderstood, but I thought that YOU thought that all the data conformed to the "(Ordered as: ...)" pattern?

1 Like
22

:slight_smile: Thank you Kristen

Not worried about which isn't "(Ordered as: ...)"
here is the sample result of the above query.. (not pasted 100 rows)

23

OK, just exclude that from the results then. Something like

SELECT TOP 100
	Col1, Col2, ...
	, CASE WHEN SummaryLine LIKE '(Ordered as: _%)%'
		THEN STUFF(LEFT(SummaryLine, CHARINDEX(')', SummaryLine)-1), 1, 13, '')
		ELSE 'N/A'
		END
FROM	MyTable
24

Perhaps worth mention that once one gets to this point - reliant on data formatting being "in a particular way" the outcome is rarely, in my experience, worth it. What it really needs is a column containing the exact and appropriate data, and not attempting to parse it out of something with a different intended purpose.

If that is not possible, given the database structure is cast-in-stone, then perhaps have your own table that maps onto it (e.g. on the same PKey column(s)) and which DOES have the appropriate data. That can be "harvested", where it does not yet exist in your table, from the data (e.g. where the desired pattern IS found) and then "acquired" where there is no data - either input manually, whatever, and can be "UNKNOWN" in other instances - users can be told if the report says "UNKNOWN" and it is critical to get you to originate the missing data, etc. Or provide the user with a Mini-APP so they can do it themselves. I've done XLS Spreadsheets, hooked up to the database, in situations where that was the only way to enable users to "provide" the necessary "Code lookup descriptions" that were needed.

Just talking out loud, might not be applicable in your use-case.

25

Below the results of the two queries,

SELECT TOP 100
summaryline
, CASE WHEN SummaryLine LIKE '(Ordered as: _%)%'
THEN STUFF(LEFT(SummaryLine, CHARINDEX(')', SummaryLine)-1), 1, 13, '')
ELSE 'N/A'
END
FROM CV3Order

select SummaryLine
from cv3Order
where summaryline like '(Ordered as: %)'

Pasted image808×101 3.47 KB

26

Surprised that "mL" in the first resultset gave you a blank in the second column.

Is my proposed solution any good to you? In the majority of rows you do not have any "(Ordered as: xxx)" which can be used, so you either need to define something different for those rows, or just display "N/A" or similar, as I have done.

27

I have to compare only rows which have "(Ordered as: xxx)"
Not worried about other rows

28

OK, sounds like you are good-to-go then.

There are several different discussion points here, have you got what you need, or do you need some help to isolate the correct bit?

(If so you'll need to explain to me what, specifically, you need help isolating / solving)

29

I did not get the correct SQL, I need help

30

I can;t guess what bit you need help with ... you need to explain that.

31

This will give you the "Ordered as: XXX" value, when such a value exists:

CASE WHEN SummaryLine LIKE '(Ordered as: _%)%'
		THEN STUFF(LEFT(SummaryLine, CHARINDEX(')', SummaryLine)-1), 1, 13, '')
		ELSE 'N/A'
		END
32

I have this, but how can I compare with the xxx value in the other table column name 'name' ?

33 
SELECT ...
FROM MyTable AS T1
   JOIN MyOtherTable AS T2
     ON T1.SomeID = T2.SomeID
WHERE 
     T2.SummaryLine LIKE '(Ordered as: _%)%' -- Separate test in case index available
    AND T1.[Name]
         = STUFF(LEFT(T2.SummaryLine, CHARINDEX(')', T2.SummaryLine)-1), 1, 13, '')

Note that from your earlier COUNT figures only 126 rows, out of a total of 127,837 in that table, will be part of this query.

1 Like
34

Wow ! that worked exactly I wanted . . Thank you so much