sql server query

I have this query I would like to get only the rows where the ascii sum is the same but the text dont match. ie

A4 Ultra s line vs a4 s line ultra with a view to later correct the string.

SELECT tw.StrShort, COUNT(tw.strshort) AS TotalClientCodes, dbo.func_ASCIIEncodeString(tw.StrShort) as asciisum
FROM TblWords tw
WHERE strfull like'a4%line%'
group BY tw.StrShort
ORDER by tw.StrShort

Open in new window


example
PeterBaileyUkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
what you intend to do with these "duplicate" rows?

the result comes with 2 rows generally because of your source (StrShor) got different values.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this and let me know the result:
;WITH CTE_CountAscii
AS (
	SELECT dbo.func_ASCIIEncodeString(StrShort) as asciisum, COUNT(1) CountAscii
	FROM TblWords
	GROUP BY dbo.func_ASCIIEncodeString(StrShort)
	HAVING COUNT(1) > 1
	)
SELECT tw.StrShort, COUNT(tw.strshort) AS TotalClientCodes, cte.asciisum
FROM TblWords tw
	INNER JOIN CTE_CountAscii cte ON tw.dbo.func_ASCIIEncodeString(tw.StrShort) = cte.asciisum
WHERE tw.strfull like'a4%line%'
GROUP BY tw.StrShort, cte.asciisum
ORDER by tw.StrShort

Open in new window

0
PeterBaileyUkAuthor Commented:
two different clients expressed the same vehicle differently, my intention is to get the rows in this set there are only two as you saw and correct one so it reads the same.

so all of the others start with A4 S LINE so the A4 ULTRA S LINE would become A4 S LINE ULTRA but I have to identify them first. the asciisum column identifies them here value 984 appearing twice tells me its the same vehicle with the words in a different order.
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

PeterBaileyUkAuthor Commented:
its giving an error

Msg 207, Level 16, State 1, Line 19
Invalid column name 'dbo'.

looks like its this line

INNER JOIN CTE_CountAscii cte ON tw.dbo.func_ASCIIEncodeString(tw.StrShort) = cte.asciisum

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry. Copy/paste issue:

;WITH CTE_CountAscii
AS (
	SELECT dbo.func_ASCIIEncodeString(StrShort) as asciisum, COUNT(1) CountAscii
	FROM TblWords
	GROUP BY dbo.func_ASCIIEncodeString(StrShort)
	HAVING COUNT(1) > 1
	)
SELECT tw.StrShort, COUNT(tw.strshort) AS TotalClientCodes, cte.asciisum
FROM TblWords tw
	INNER JOIN CTE_CountAscii cte ON dbo.func_ASCIIEncodeString(tw.StrShort) = cte.asciisum
WHERE tw.strfull like'a4%line%'
GROUP BY tw.StrShort, cte.asciisum
ORDER by tw.StrShort

Open in new window

0
PeterBaileyUkAuthor Commented:
its returning the same number of rows
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
What this returns?
SELECT dbo.func_ASCIIEncodeString(StrShort) as asciisum, COUNT(1) CountAscii
FROM TblWords
GROUP BY dbo.func_ASCIIEncodeString(StrShort)
HAVING COUNT(1) > 1

Open in new window

0
PeterBaileyUkAuthor Commented:
it returns a lot, i ordered the output and looked at 984 which has 1522 in the count column. at this upper level it is possible to have different text and ascii sums equal.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I've reread your requirements and I think I went to a wrong direction.
Please try this one:
SELECT tw.StrShort, COUNT(tw.strshort) AS TotalClientCodes, dbo.func_ASCIIEncodeString(tw.StrShort)
FROM TblWords tw
WHERE NOT EXISTS (SELECT 1
		FROM TblWords tw2
		WHERE dbo.func_ASCIIEncodeString(tw.StrShort) = dbo.func_ASCIIEncodeString(tw2.StrShort)
			AND tw.StrShort <> tw2.StrShort)
GROUP BY tw.StrShort, dbo.func_ASCIIEncodeString(tw.StrShort)
ORDER by tw.StrShort

Open in new window

0
PortletPaulEE Topic AdvisorCommented:
Peter, for future reference consider how much easier it would be if we had that sample of data as text we could put into a table.

While images are useful, data is vastly more useful.
0
PeterBaileyUkAuthor Commented:
In light of Paul's comments ive attached a small spreadsheet but if your on a mobile device might be wise to leave it.

Ive attached the output from ID: 42050836 too I wonder if a better way to go is to find the rows where it has the same words but in different order in without using the ascii sum.
example.xlsx
0
PortletPaulEE Topic AdvisorCommented:
Here's a somewhat different approach, but it may not scale too well:
DECLARE  @TblWords TABLE
    ([StrShort] varchar(15), [TotalClientCodes] int, [asciisum] int)
;
    
INSERT INTO @TblWords 
    ([StrShort], [TotalClientCodes], [asciisum])
VALUES
    ('A4 LINE ULTRA', 164, 984),
    ('A4 ULTRA S LINE', 45, 984),
    ('A4 20 S LINE', 26, 960)
;
SELECT DISTINCT
      asciisum, ca1.StrShorts, ca2.TotalClientCodes
FROM @TblWords as T
CROSS APPLY (
      SELECT
            STUFF((
                  SELECT DISTINCT
                        ', ' + StrShort
                  FROM @TblWords AS S
                  WHERE T.asciisum = s.asciisum
                  FOR xml PATH ('')
            )
            , 1, 1, '')
    ) ca1 (StrShorts)
CROSS APPLY (
      SELECT
            STUFF((
                  SELECT DISTINCT
                        ', ' + cast(TotalClientCodes as varchar(20))
                  FROM @TblWords AS S
                  WHERE T.asciisum = s.asciisum
                  FOR xml PATH ('')
            )
            , 1, 1, '')
    ) ca2 (TotalClientCodes)

Open in new window

sample result:
+---+----------+---------------------------------+------------------+
|   | asciisum |            StrShorts            | TotalClientCodes |
+---+----------+---------------------------------+------------------+
| 1 |      960 |  A4 20 S LINE                   |               26 |
| 2 |      984 |  A4 LINE ULTRA, A4 ULTRA S LINE |          164, 45 |
+---+----------+---------------------------------+------------------+

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
ha, we crossed over
0
PortletPaulEE Topic AdvisorCommented:
Hmmmm that data has not much to reveal as far as I see. I ran it through the same query as above:
+-----+----------+--------------------------------------------------------------+------------------+
|     | asciisum |                          StrShorts                           | TotalClientCodes |
+-----+----------+--------------------------------------------------------------+------------------+
|   1 |        0 | NULL                                                         |          0, 1218 |
|   2 |       49 |  1                                                           |               10 |
|   3 |       50 |  2                                                           |               22 |
|   4 |       54 |  6                                                           |               39 |
|   5 |       56 |  8                                                           |                6 |
|   6 |       57 |  9                                                           |               93 |
|   7 |       65 |  A                                                           |              169 |
|   8 |       66 |  B                                                           |               52 |
|   9 |       67 |  C                                                           |               12 |
|  10 |       69 |  E                                                           |               15 |
|  11 |       70 |  F                                                           |               35 |
|  12 |       71 |  G                                                           |               95 |
|  13 |       72 |  H                                                           |                8 |
|  14 |       73 |  I                                                           |               50 |
|  15 |       74 |  J                                                           |                1 |
|  16 |       75 |  K                                                           |               22 |
|  17 |       76 |  L                                                           |              135 |
|  18 |       77 |  M                                                           |               44 |
|  19 |       78 |  N                                                           |                7 |
|  20 |       80 |  P                                                           |                6 |
|  21 |       82 |  R                                                           |              105 |
|  22 |       83 |  S                                                           |              368 |
|  23 |       85 |  U                                                           |                7 |
|  24 |       86 |  V                                                           |               10 |
|  25 |       88 |  X                                                           |               14 |
|  26 |       90 |  Z                                                           |               30 |
|  27 |       97 |  10                                                          |               18 |
|  28 |      111 |  96                                                          |                1 |
|  29 |      112 |  88                                                          |                3 |
|  30 |      115 |  A2                                                          |              114 |
|  31 |      131 |  M6                                                          |               24 |
|  32 |     2406 |  LAND CRUISER COLORADO LC4 8SEATS D4D                        |                6 |
|  33 |     2444 |  SERIES 530I AUTHORITIES SMG TOURING                         |                6 |
|  34 |     2448 |  AURIS VALVEMATIC COLOUR COLLECTION                          |                4 |
|  35 |     2469 |  GLACLASS 200 4MATIC SPORT PREMIUM CDI                       |               10 |
|  36 |     2531 |  GOLF S TSI BLUEMOTION TECHNOLOGY DSG                        |                6 |
|  37 |     2540 |  CCLASS C300 HYBRID SPORT PREMIUM PLUS                       |               21 |
|  38 |     2562 |  FOCUS 15 TDCI 120PS RED BLACK STLINE DCT                    |               18 |
|  39 |     2603 |  SERIES 3 318D LUXURY GRAN TURISMO AUTO                      |                8 |
|  40 |     2606 |  GLACLASS GLA 200 4MATIC AMG LINE PREMIUM                    |                8 |
|  41 |     2610 |  SERIES 330D XDRIV M SPORT GRAN TURISMO                      |               10 |
|  42 |     2621 |  INSIGNIA ON GRAND SPORT DESIGN TURBO D                      |               16 |
|  43 |     2627 |  GLACLASS 21 GLA 220 D AMG SPORT 4MATIC DCT                  |                9 |
|  44 |     2632 |  GLACLASS ON 200 WHITEART PREMUIM PLUS D                     |               16 |
|  45 |     2633 |  NT400 CABSTAR 3410 FREEZER REFRIDGERATED                    |               19 |
|  46 |     2670 |  FREELANDER TD4 MASAI MARA STATION WAGON                     |               12 |
|  47 |     2688 |  C4 PICASSO AIRDREAM EXCLUSIVE PLUS ETG6                     |                7 |
|  48 |     2692 |  DUCATO ADRIA SUN LIVING LIDO S 42 DF AUTO                   |                9 |
|  49 |     2694 |  GRAND VOYAGER TOURING 25TH ANNIVERSARY                      |                8 |
|  50 |     2697 |  MOVANO ON L1H1 F3300 COMBI SS CDTI ECOFLEX                  |                9 |
|  51 |     2699 |  SPRINTER AUTOSLEEPERS WINCHCOMBE AUTO                       |                4 |
|  52 |     2704 |  BONNEVILLE STREET SCRAMBLER PREM PAINT                      |                6 |
|  53 |     2707 |  NV300 ON TEKNA L2H1 SS CREW DCI TWINTURBO                   |               18 |
|  54 |     2709 |  RANGE ROVER V8 SVAUTOBIOGRAPHY DYNAMIC                      |                5 |
|  55 |     2712 |  GRAND CHEROKEE 75TH ANNIVERSARY EDITION                     |                5 |
|  56 |     2744 |  NT400 CABSTAR PRO FREEZER BOX 1127 PAYLOAD                  |               10 |
|  57 |     2752 |  SCENIC XMOD DYNAMIQUE TOMTOM ENERGY TCE                     |               16 |
|  58 |     2759 |  VIVARO 2900 L1H1 CDTI DCB SPORTIVE ECOFLEX                  |               56 |
|  59 |     2766 |  SPACETOURER BLUEHDI 150HP SS XL BUSINESS                    |                7 |
|  60 |     2776 |  DISPATCH M 1000 ENTERPRISE BLUEHDI SS ETG6                  |                7 |
|  61 |     2777 |  DISPATCH ON ENTERPRISE XS BLUEHDI 115 SWB                   |                8 |
|  62 |     2781 |  MASTER ON SM35 BUSINESS ENERGY DCI 145 SWB                  |                9 |
|  63 |     2789 |  NEW CODE VL 1600 KMM95INTRUDERMARAUDERBOU                   |                6 |
|  64 |     2791 |  SPRINTER ON 311 BE CDI BLUEEFFICIENCY ELWB                  |               24 |
|  65 |     2807 |  TRAFIC LL29 BUSINESS PLUS ENERGY DCI CREW                   |               14 |
|  66 |     2817 |  TRANSPORTER ON T30 KOMBI HIGHLINE BMT SWB                   |               18 |
|  67 |     2824 |  TRAVELLER BUSINESSSTANDARD BLUEHDI115 SS                    |                4 |
|  68 |     2828 |  CCLASS C230 KOMPRESSOR EVO PANORAMA AMG SE                  |               35 |
|  69 |     2831 |  VITO TOURER ON 114 SELECT LONG BLUETEC LWB                  |                9 |
|  70 |     2832 |  CCLASS C180 KOMPRESSOR EVO PANORAMA AMG SE                  |               21 |
|  71 |     2838 |  TRAVELLER ON ALLURE COMPACT BLUEHDI 180 SS                  |                8 |
|  72 |     2839 |  MX5 RF CONVERTIBLE SPECIAL EDITION LAUNCH                   |                6 |
|  73 |     2854 |  TRANSIT CUSTOM ON 290 L1H1 LIMITED TDCI SWB                 |               18 |
|  74 |     2868 |  NT400 CABSTAR 3514 FREEZER SINGLE CAB DCI MWB               |               11 |
|  75 |     2873 |  MASTER MML45 BUSINESS ENERGY DCI SR PV DRW                  |                8 |
|  76 |     2875 |  SANDERO STEPWAY DIESEL HATCHBACK LAUREATE                   |                5 |
|  77 |     2878 |  INSIGNIA ON SPORTS TOURER SRI NAV TURBO D                   |               36 |
|  78 |     2879 |  I8 COUPE SPECIAL EDITION PROTRONIC SILVER                   |                6 |
|  79 |     2884 |  INSIGNIA ON GRAND SPORT SRI TURBO D ECOTEC                  |               18 |
|  80 |     2888 |  BERLINGO MULTISPACE ON FLAIR BLUEHDI 100 SS                 |                8 |
|  81 |     2889 |  NT400 CABSTAR ON 3513 DOUBLE DROPSIDE DCI LWB               |                9 |
|  82 |     2896 |  NT400 CABSTAR ON 3513 SINGLE DROPSIDE DCI LWB               |               18 |
|  83 |     2899 |  MASTER LLL35 BUSINESS ENERGY DCI LR DCC DRW                 |                8 |
|  84 |     2901 |  TRAVELLER ON ACTIVE STANDARD BLUEHDI 115 SS                 |                8 |
|  85 |     2905 |  TRANSPORTER ON T32 KOMBI TRENDLINE BMT LWB                  |               18 |
|  86 |     2907 |  MASTER ON LHL35 BUSINESS ENERGY DCI 145 ELWB                |                9 |
|  87 |     2909 |  TRAVELLER ON ALLURE STANDARD BLUEHDI 150 SS                 |                8 |
|  88 |     2913 |  DISPATCH XL 1400 ENTERPRISE PLUS BLUEHDI SS                 |               14 |
|  89 |     2914 |  MAZDA6 22 175PS PLATINUM SAFETY CRUISE P AUT                |                9 |
|  90 |     2915 |  TRAFIC SL29 BUSINESS PLUS ENERGY DCI LR CRC                 |               16 |
|  91 |     2922 |  TRANSPORTER ON T32 KOMBI STARTLINE BMT LWB                  |               18 |
|  92 |     2924 |  LEON DIESEL HATCHBACK XCELLENCE TECHNOLOGY                  |               20 |
|  93 |     2927 |  TRANSPORTER ON T30 KOMBI STARTLINE BMT SWB                  |               18 |
|  94 |     2929 |  TRANSPORTER ON T32 KOMBI STARTLINE BMT SWB                  |               18 |
|  95 |     2930 |  GLACLASS GLA200 4MATIC AMG LINE PREMIUM PLUS                |                8 |
|  96 |     2932 |  GLACLASS GLA220 4MATIC AMG LINE PREMIUM PLUS                |               16 |
|  97 |     2938 |  SPRINTER LUNAR CRISTALL LANDSTAR RLS AUTO                   |                6 |
|  98 |     2942 |  CRV EX HONDA CONNECT WTH NAVI HONDA SENSING                 |               32 |
|  99 |     2944 |  SERIES 320D XDRIVE M SPORT GRAN TURISMO AUT                 |               20 |
| 100 |     2949 |  SERIES 320I XDRIVE M SPORT GRAN TURISMO AUT                 |               30 |
| 101 |     2951 |  DOBLO CARGO SX MULTIJET COMFORTMATIC COMBI                  |               14 |
| 102 |     2953 |  DUCATO BURSTNER IXEO TIME SOVEREIGN IT745 A                 |                7 |
| 103 |     2956 |  DUCATO BURSTNER IXEO TIME SOVEREIGN IT586 A                 |                7 |
| 104 |     2961 |  DISPATCH COMBI 6 L1H1 90 HDI MWB STANDARD ROOF              |               11 |
| 105 |     2964 |  TRANSPORTER ON T32 HIGHLINE 4MOTION BMT LWB                 |               30 |
| 106 |     2967 |  Z SERIES SDRIVE30I M SPORT HIGHLINE EDITION                 |               20 |
| 107 |     2968 |  CCLASS HYBRID ON C350 AMG LINE PREMIUM PLUS E               |               20 |
| 108 |     2970 |  GRAND SCENIC DYNAMIQUE TOMTOM ENERGY DCI 110                |               10 |
| 109 |     2973 |  GLECLASS GLE 500 4MATIC AMG LINE PREMIUM PLUS               |                9 |
| 110 |     2975 |  JUKE ACENTA PREMIUM COMFORT EXTERIOR DIGT 1                 |               18 |
| 111 |     2976 |  GLECLASS GLE 350 4MATIC AMG LINE PREMIUM PLUS               |               18 |
| 112 |     2983 |  SCENIC GRAND DYNAMIQUE TOMTOM BOSE PLUS EDC                 |                7 |
| 113 |     2984 |  GRAND SCENIC DYNAMIQUE TOMTOM ENERGY TCE 130                |               10 |
| 114 |     2993 |  XTRAIL ARCTIX EXPEDITION SPORTS ADVENTURE                   |               10 |
| 115 |     2994 |  VESPA GTSGTV 300 IE SUPERVIA MONTENAPOLEONE                 |                6 |
| 116 |     2998 |  GRAND SCENIC DYNAMIQUE TOMTOM BOSE PACK VVT                 |                7 |
| 117 |     2999 |  ACLASS ON AMG A45 4MATIC YELLOW NIGHT EDITION               |               10 |
| 118 |     3003 |  TRAVELLER ON BUSINESS COMPACT BLUEHDI 115 SS                |               16 |
| 119 |     3009 |  GLACLASS ON 220 4MATIC AMG LINE PREMIUM PLUS D              |               11 |
| 120 |     3012 |  SCENIC HYBRID ON SIGNATURE NAV DCI 110 ASSIST               |                9 |
| 121 |     3014 |  TRANSPORTER T32 KOMBI SPORTLINE 60 BITDI LWB                |               20 |
| 122 |     3022 |  VITO TOURER ON 116 SELECT X LONG BLUETEC ELWB               |               10 |
| 123 |     3025 |  TALENTO ON SX 12 HR ECOJET 145 SS LWB HIGH ROOF             |               12 |
| 124 |     3026 |  VITO 116 COMPACT TRAVELINE CDI MWB MEDIUM ROO               |               11 |
| 125 |     3030 |  TALENTO ON SX 12 HR ECOJET 125 SS SWB HIGH ROOF             |               12 |
| 126 |     3034 |  RANGE ROVER SPORT HYBRID ON HSE DYNAMIC SDV6                |               10 |
| 127 |     3035 |  FORFOUR ON NIGHT SKY BRABUS SPORT PREMIUM T                 |               18 |
| 128 |     3036 |  TRAVELLER ON BUSINESS STANDARD BLUEHDI 95 SS                |               16 |
| 129 |     3037 |  MULTISTRADA 950 STAR WHITE SILK TOURING PACK                |               10 |
| 130 |     3039 |  GLACLASS GLA220 CDI 4MATIC SPORT PREMIUM PLUS               |                7 |
| 131 |     3040 |  DUCATO ADRIA MATRIX SILVER COLLECTION M670SL                |                6 |
| 132 |     3042 |  GLACLASS GLA 250 4MATIC WHITEART PREMIUM PLUS               |                7 |
| 133 |     3043 |  IS HYBRID ON 300H EXECUTIVE EDITION DUAL VVTI               |                9 |
| 134 |     3044 |  TIGUAN SPORT 4MOTION BLUEMOTION TECHNOLOGY                  |               18 |
| 135 |     3049 |  SCENIC XMOD DYNAMIQUETT BOSEPLUS ENERGY DCI                 |               14 |
| 136 |     3050 |  MINI JOHN COOPER WORKS WORLD CHAMPIONSHIP 50                |               20 |
| 137 |     3051 |  KANGOO MAXI LL21 BUSINESS PLUS ENERGY CRC DCI               |               16 |
| 138 |     3053 |  VITO TOURER ON 114 SELECT COMPACT BLUETEC SWB               |                9 |
| 139 |     3054 |  MASTER LL35 BUSINESS ENERGY DCI LR DCC TIPPER               |                8 |
| 140 |     3057 |  TRANSPORTER ON T32 TRENDLINE 4MOTION BMT LWB                |               10 |
| 141 |     3060 |  INSIGNIA ON GRAND SPORT TECH LINE NAV TURBO D               |               40 |
| 142 |     3062 |  DISCOVERY SW SPECIAL EDITIONS FIRST EDITION                 |                6 |
| 143 |     3066 |  DMAX ARCTIC TRUCKS DOUBLE CAB MWB MEDIUM ROOF               |               22 |
| 144 |     3067 |  DMAX ARCTIC TRUCKS EXTENDED CAB MWB MEDIUM RO               |               11 |
| 145 |     3076 |  TRAVELLER ON BUSINESS STANDARD BLUEHDI 150 SS               |               16 |
| 146 |     3078 |  GALLARDO LP5704 SUPERLEGGERA EDIZIONE TECNICA               |                7 |
| 147 |     3080 |  INSIGNIA ON GRAND SPORT SRI VXLINE NAV TURBO                |                9 |
| 148 |     3089 |  MASTER ML35 BUSINESS ENERGY DCI LRCC DROPSIDE               |               14 |
| 149 |     3090 |  MASTER ML35 BUSINESS ENERGY DCI LR TIPPER DRW               |                8 |
| 150 |     3091 |  JUKE ACENTA PREMIUM SAFETY EXTERIOR PURE DRI                |                9 |
| 151 |     3093 |  FLHTCUSE3 SCREAMIN EAGLE ULTRA CLASSIC EGLIDE               |                7 |
| 152 |     3108 |  MASTER LLL35 BUSINESS ENERGYDCI LRCC DROP DRW               |               14 |
| 153 |     3115 |  MASTER ON LML35 TW BUSINESS ENERGY DCI 145 ELWB             |               20 |
| 154 |     3122 |  SCENIC HYBRID ON DYNAMIQUE S NAV DCI 110 ASSIST             |               10 |
| 155 |     3123 |  RANGE ROVER AUTOBIOGRAPHY BLACK SUPERCHARGED                |                9 |
| 156 |     3125 |  BONNEVILLE STREET BRAT TRACKER PREMIUM PAINT                |                8 |
| 157 |     3126 |  EXPERT ON PROFESSIONAL LONG BLUEHDI 120 SS MWB              |                9 |
| 158 |     3128 |  CHEROKEE 75TH ANNIVERSARY ACTIVEDRIVE II MJET               |                8 |
| 159 |     3130 |  GOLF SE NAVIGATION TSI BLUEMOTION TECHNOLOGY                |               24 |
| 160 |     3133 |  DISPATCH M 1250 ENTERPRISE PLUS BLUEHDI SS EAT6             |                8 |
| 161 |     3139 |  GLACLASS GLA 220 D 4MATIC WHITEART PREMIUM PLUS             |                8 |
| 162 |     3140 |  DOBLO CARGO MAXI CRC SX MULTIJET COMFORTMATIC               |                8 |
| 163 |     3142 |  COMBO ON SPORTIVE 2300 L2H1 SS CREW CDTI ECOFLEX            |               10 |
| 164 |     3143 |  TRAFIC ON LL29 SPORT NAV CREW ENERGY DCI 145 SWB            |               11 |
| 165 |     3145 |  INSIGNIA ON GRAND SPORT SRI NAV TURBO D ECOTEC              |               20 |
| 166 |     3150 |  TRAFIC ON SL29 SPORT NAV CREW ENERGY DCI 145 SWB            |               11 |
| 167 |     3151 |  KANGOO ON MAXI LL21 CREW BUSINESS ENERGY DCI 110            |               10 |
| 168 |     3157 |  SCLASS S300 L AMG LINE EXECUTIVE PREMIUM PLUS H             |               12 |
| 169 |     3160 |  INSIGNIA ON SPORTS TOURER TECH LINE NAV TURBO               |                9 |
| 170 |     3166 |  VIVARO ON 2700 L1H1 SPORTIVE CDTI BLUEINJECTION             |                8 |
| 171 |     3168 |  VIVARO ON 2900 L1H1 SPORTIVE CDTI BLUEINJECTION             |               16 |
| 172 |     3169 |  VIVARO ON 2900 L2H1 SPORTIVE CDTI BLUEINJECTION             |               16 |
| 173 |     3171 |  SCENIC XMOD DYNAMIQUE TOMTOM BOSE PLUS DCI EDC              |                8 |
| 174 |     3172 |  GLACLASS GLA220 CDI 4MATIC AMG LINE PREMIUM PLUS            |                8 |
| 175 |     3176 |  NT400 CABSTAR 3512 HIGH PAYLOAD SINGLE CAB DCI LWB          |               12 |
| 176 |     3177 |  NT400 CABSTAR 3512 HIGH PAYLOAD SINGLE CAB DCI MWB          |               12 |
| 177 |     3185 |  XL 1200C SPORTSTER 1200 CUSTOM A 110TH ANIVERSARY           |                9 |
| 178 |     3186 |  XL 1200C SPORTSTER 1200 CUSTOM B 110TH ANIVERSARY           |                9 |
| 179 |     3188 |  INSIGNIA ON SPORTS TOURER DESIGN TURBO ECOTEC               |                8 |
| 180 |     3189 |  DUCATO ADRIA MATRIX PLATINUM COLLECTION M670SL              |                6 |
| 181 |     3195 |  CLUBMAN ON JOHN COOPER WORKS CHALLENGE EDITION              |                8 |
| 182 |     3201 |  LAGUNA DYNAMIQUE TOMTOM INITIALE LUXE PACK DCI              |               84 |
| 183 |     3202 |  FORFOUR NIGHT SKY BRABUS SPORT PREMIUM PLUS T               |               16 |
| 184 |     3207 |  SCENIC GRAND DYNAMIQUE TOMTOM LUXE PACK DCI EDC             |                8 |
| 185 |     3209 |  FORFOUR ON NIGHT SKY PRIME SPORT PREMIUM PLUS               |               18 |
| 186 |     3212 |  LEON DIESEL SPORT TOURER XCELLENCE TECHNOLOGY               |               24 |
| 187 |     3220 |  DISPATCH XL 1250 ENTERPRISE PLUS BLUEHDI SS EAT6            |                8 |
| 188 |     3223 |  DISPATCH XS 1300 ENTERPRISE PLUS BLUEHDI SS EAT6            |                8 |
| 189 |     3227 |  DISPATCH ON ENTERPRISE PLUS M BLUEHDI 120 SS MWB            |               10 |
| 190 |     3228 |  MEGANE SCENIC THE ULTIMATE PRIVILEGE MONACO DCI             |                7 |
| 191 |     3229 |  SERIES 320D EFFICIENTDYNAMICS BUSINESS TOURING              |               36 |
| 192 |     3230 |  DISPATCH ON ENTERPRISE PLUS M BLUEHDI 150 SS MWB            |               10 |
| 193 |     3236 |  RANGE ROVER EVOQUE TD4 180HP AUTOBIOGRAPHY AUTO             |                8 |
| 194 |     3249 |  INSIGNIA ON GRAND SPORT DESIGN NAV TURBO ECOTEC             |                9 |
| 195 |     3254 |  LEON DIESEL SPORT TOURER SE DYNAMIC TECHNOLOGY              |               21 |
| 196 |     3255 |  LEON DIESEL SPORT COUPE FR TITANIUM TECHNOLOGY              |               28 |
| 197 |     3256 |  VIVARO ON 2700 L1H1 SPORTIVE CDTI BITURBO SS BLUE           |               20 |
| 198 |     3258 |  VIVARO ON 2900 L1H1 SPORTIVE CDTI BITURBO SS BLUE           |               40 |
| 199 |     3259 |  VIVARO ON 2900 L2H1 SPORTIVE CDTI BITURBO SS BLUE           |               40 |
| 200 |     3260 |  INSIGNIA ON SPORTS TOURER TECH LINE NAV TURBO D             |               40 |
| 201 |     3261 |  XTRAIL STATION WAGON SPORT EXPEDITION EXTREME               |               27 |
| 202 |     3263 |  VIVARO ON 2700 L1H1 CDTI BITURBO SS BLUEINJECTION           |                9 |
| 203 |     3265 |  VIVARO ON 2900 L1H1 CDTI BITURBO SS BLUEINJECTION           |               36 |
| 204 |     3278 |  INSIGNIA ON GRAND SPORT ELITE NAV TURBO D ECOTEC            |               20 |
| 205 |     3279 |  XTRAIL STATION WAGON ARCTIX EXPEDITION SPORT A              |               18 |
| 206 |     3280 |  INSIGNIA ON SPORTS TOURER SRI VXLINE NAV TURBO              |                9 |
| 207 |     3286 |  VIVARO ON 2700 L1H1 LIMITED EDITION NAV CDTI BITUR          |               20 |
| 208 |     3289 |  VIVARO ON 2900 L2H1 LIMITED EDITION NAV CDTI BITUR          |               30 |
| 209 |     3299 |  GRAND SCENIC DYNAMIQUE TOMTOM BOSE ENERGY DCI 110           |               11 |
| 210 |     3301 |  GRAND SCENIC DYNAMIQUE TOMTOM BOSE ENERGY DCI 130           |               11 |
| 211 |     3311 |  TRANSIT CUSTOM 290 L1 DIESEL FWD SPECIAL EDITIONS           |                9 |
| 212 |     3313 |  GRAND SCENIC DYNAMIQUE TOMTOM BOSE ENERGY TCE 130           |               11 |
| 213 |     3314 |  DISPATCH ON ENTERPRISE PLUS XL BLUEHDI 120 SS MWB           |               10 |
| 214 |     3316 |  GRAND SCENIC DYNAMIQUE TOMTOM BOSE ENERGY TCE 115           |               11 |
| 215 |     3317 |  DISPATCH ON ENTERPRISE PLUS XL BLUEHDI 150 SS MWB           |               10 |
| 216 |     3320 |  DISPATCH ON ENTERPRISE PLUS XL BLUEHDI 180 SS MWB           |               10 |
| 217 |     3325 |  FORFOUR ON NIGHT SKY PRIME SPORT PREMIUM PLUS T             |               20 |
| 218 |     3327 |  DISPATCH ON ENTERPRISE PLUS XS BLUEHDI 120 SS SWB           |               10 |
| 219 |     3329 |  EXPERT ON PROFESSIONAL PLUS LONG BLUEHDI 120 SS M           |               10 |
| 220 |     3330 |  DISPATCH ON ENTERPRISE PLUS XS BLUEHDI 150 SS SWB           |               10 |
| 221 |     3332 |  EXPERT ON PROFESSIONAL PLUS LONG BLUEHDI 150 SS M           |               10 |
| 222 |     3333 |  DISPATCH ON ENTERPRISE PLUS XS BLUEHDI 180 SS SWB           |               10 |
| 223 |     3335 |  EXPERT ON PROFESSIONAL PLUS LONG BLUEHDI 180 SS M           |               10 |
| 224 |     3345 |  INSIGNIA ON SPORTS TOURER SRI NAV TURBO D ECOTEC            |               20 |
| 225 |     3348 |  GIULIETTA TBI QUADRIFOGLIO VERDE TCT LAUNCH EDTN            |                7 |
| 226 |     3350 |  TRAVELLER ON BUSINESS VIP STANDARD BLUEHDI 180 SS           |                9 |
| 227 |     3351 |  EXPERT ON PROFESSIONAL COMPACT BLUEHDI 115 SS SWB           |                9 |
| 228 |     3353 |  EXPERT ON PROFESSIONAL STANDARD BLUEHDI 115 SS MW           |                9 |
| 229 |     3355 |  EXPERT ON PROFESSIONAL PLUS COMPACT BLUEHDI 150 S           |                9 |
| 230 |     3356 |  RANGE ROVER SPORT AUTOBIOGRAPHY SUPERCHARGED V8             |                9 |
| 231 |     3358 |  EXPERT ON PROFESSIONAL PLUS COMPACT BLUEHDI 180 S           |                9 |
| 232 |     3381 |  TOURNEO CONNECT 15 CDTI 100PS ZETEC POWERSHIFT DCT          |                8 |
| 233 |     3384 |  GOLF SE NAVIGATION TSI BLUEMOTION TECHNOLOGY DSG            |               14 |
| 234 |     3391 |  FORFOUR ON NIGHT SKY BRABUS SPORT PREMIUM PLUS T            |               20 |
| 235 |     3403 |  GRAND SCENIC HYBRID ON DYNAMIQUE NAV DCI 110 ASSIST         |               10 |
| 236 |     3408 |  GRAND SCENIC HYBRID ON SIGNATURE NAV DCI 110 ASSIST         |               10 |
| 237 |     3420 |  EXPERT ON PROFESSIONAL PLUS STANDARD BLUEHDI 120 M          |                9 |
| 238 |     3426 |  EXPERT ON PROFESSIONAL PLUS STANDARD BLUEHDI 120 S          |                9 |
| 239 |     3429 |  EXPERT ON PROFESSIONAL PLUS STANDARD BLUEHDI 150 S          |                9 |
| 240 |     3432 |  EXPERT ON PROFESSIONAL PLUS STANDARD BLUEHDI 180 S          |                9 |
| 241 |     3440 |  RANGE ROVER EVOQUE TD4 180HP SE DYNAMIC CONVER AUTO         |               11 |
| 242 |     3445 |  RANGE ROVER EVOQUE TD4 180HP HSE DYNAMI CONVER AUTO         |               11 |
| 243 |     3449 |  INSIGNIA ON SPORTS TOURER DESIGN NAV TURBO ECOTEC           |                9 |
| 244 |     3450 |  TRANSPORTER ON T32 KOMBI HIGHLINE 4MOTION BMT LWB 4         |               11 |
| 245 |     3459 |  TRANSPORTER ON T32 KOMBI TRENDLINE 4MOTION BMT LWB          |               10 |
| 246 |     3463 |  RANGE ROVER EVOQUE TD4 180HP SE DYNAMIC CONVERTIBLE         |               10 |
| 247 |     3466 |  TRANSPORTER ON T32 KOMBI TRENDLINE 4MOTION BMT SWB          |               10 |
| 248 |     3468 |  RANGE ROVER EVOQUE TD4 180HP HSE DYNAMI CONVERTIBLE         |               10 |
| 249 |     3476 |  TRANSPORTER ON T32 KOMBI STARTLINE 4MOTION BMT LWB          |               10 |
| 250 |     3478 |  INSIGNIA ON SPORTS TOURER ELITE NAV TURBO D ECOTEC          |               30 |
| 251 |     3483 |  TRANSPORTER ON T32 KOMBI STARTLINE 4MOTION BMT SWB          |               10 |
| 252 |     3511 |  INSIGNIA ON GRAND SPORT SRI VXLINE NAV TURBO D ECOT         |               11 |
| 253 |     3518 |  GRAND SCENIC HYBRID ON DYNAMIQUE S NAV DCI 110 ASSIST       |               11 |
| 254 |     3527 |  INSIGNIA ON GRAND SPORT TECH LINE NAV TURBO D ECOTEC        |               22 |
| 255 |     3534 |  DUCATO ADRIA MATRIX PLATINUM COLLECTION M670SL AUTO         |                7 |
| 256 |     3535 |  PATHFINDER STATION WAGON MAMMOTH SPORTS ADVENTURE           |               16 |
| 257 |     3536 |  DS 4 HATCHBACK SPECIAL EDITION CROSSBACK TERRE ROUGE        |                8 |
| 258 |     3548 |  INSIGNIA ON SPORTS TOURER SRI VXLINE NAV TURBO D EC         |               11 |
| 259 |     3549 |  INSIGNIA ON SPORTS TOURER DESIGN NAV TURBO D ECOTEC         |               20 |
| 260 |     3590 |  GRAND ESPACE DYNAMIQUE TOMTOM INITIALE LUXE PACK DCI        |                8 |
| 261 |     3591 |  INSIGNIA ON SPORTS TOURER TECH LINE NAV TURBO D ECOT        |               22 |
| 262 |     3621 |  TOURNEO CONNECT 15 CDTI 100PS TITANIUM POWERSHIFT DCT       |                8 |
| 263 |     3638 |  VIVARO L1H1 2700 LIMITED EDITION NAV CDTI PV BITURBO SS     |               20 |
| 264 |     3641 |  VIVARO L2H1 2900 LIMITED EDITION NAV CDTI PV BITURBO SS     |               20 |
| 265 |     3675 |  VIVARO L1H1 2900 LIMITED EDITION NAV CDTI DCB BITURBO SS    |               20 |
| 266 |     3676 |  VIVARO L2H1 2900 LIMITED EDITION NAV CDTI DCB BITURBO SS    |               20 |
| 267 |     3771 |  TRANSIT CUSTOM ON 290 L1H1 TREND COLOUR EDITION TDCI SWB    |               11 |
| 268 |     3777 |  GRAND TOURNEO CONNECT 15 CDTI 100PS ZETEC POWERSHIFT DCT    |                9 |
| 269 |     3858 |  TRANSPORTER ON T32 KOMBI SPORTLINE BITDI BLUEMOTION TEC     |               45 |
| 270 |     3886 |  TRANSPORTER ON T32 KOMBI HIGHLINE BLUEMOTION TECHNOLOGY     |                8 |
| 271 |     4017 |  GRAND TOURNEO CONNECT 15 CDTI 100PS TITANIUM POWERSHIFT DCT |                9 |
+-----+----------+--------------------------------------------------------------+------------------+

Open in new window

the data as inserts:
 DECLARE @TblWords TABLE
    ([StrShort] varchar(59), [TotalClientCodes] int, [asciisum] int)
;
    
INSERT INTO @TblWords 
    ([StrShort], [TotalClientCodes], [asciisum])
VALUES
    (NULL, 0, 0),
    (NULL, 1218, 0),
    ('1', 10, 49),
    ('10', 18, 97),
    ('2', 22, 50),
    ('6', 39, 54),
    ('8', 6, 56),
    ('88', 3, 112),
    ('9', 93, 57),
    ('96', 1, 111),
    ('A', 169, 65),
    ('A2', 114, 115),
    ('ACLASS ON AMG A45 4MATIC YELLOW NIGHT EDITION', 10, 2999),
    ('AURIS VALVEMATIC COLOUR COLLECTION', 4, 2448),
    ('B', 52, 66),
    ('BERLINGO MULTISPACE ON FLAIR BLUEHDI 100 SS', 8, 2888),
    ('BONNEVILLE STREET BRAT TRACKER PREMIUM PAINT', 8, 3125),
    ('BONNEVILLE STREET SCRAMBLER PREM PAINT', 6, 2704),
    ('C', 12, 67),
    ('C4 PICASSO AIRDREAM EXCLUSIVE PLUS ETG6', 7, 2688),
    ('CCLASS C180 KOMPRESSOR EVO PANORAMA AMG SE', 21, 2832),
    ('CCLASS C230 KOMPRESSOR EVO PANORAMA AMG SE', 35, 2828),
    ('CCLASS C300 HYBRID SPORT PREMIUM PLUS', 21, 2540),
    ('CCLASS HYBRID ON C350 AMG LINE PREMIUM PLUS E', 20, 2968),
    ('CHEROKEE 75TH ANNIVERSARY ACTIVEDRIVE II MJET', 8, 3128),
    ('CLUBMAN ON JOHN COOPER WORKS CHALLENGE EDITION', 8, 3195),
    ('COMBO ON SPORTIVE 2300 L2H1 SS CREW CDTI ECOFLEX', 10, 3142),
    ('CRV EX HONDA CONNECT WTH NAVI HONDA SENSING', 32, 2942),
    ('DISCOVERY SW SPECIAL EDITIONS FIRST EDITION', 6, 3062),
    ('DISPATCH COMBI 6 L1H1 90 HDI MWB STANDARD ROOF', 11, 2961),
    ('DISPATCH M 1000 ENTERPRISE BLUEHDI SS ETG6', 7, 2776),
    ('DISPATCH M 1250 ENTERPRISE PLUS BLUEHDI SS EAT6', 8, 3133),
    ('DISPATCH ON ENTERPRISE PLUS M BLUEHDI 120 SS MWB', 10, 3227),
    ('DISPATCH ON ENTERPRISE PLUS M BLUEHDI 150 SS MWB', 10, 3230),
    ('DISPATCH ON ENTERPRISE PLUS XL BLUEHDI 120 SS MWB', 10, 3314),
    ('DISPATCH ON ENTERPRISE PLUS XL BLUEHDI 150 SS MWB', 10, 3317),
    ('DISPATCH ON ENTERPRISE PLUS XL BLUEHDI 180 SS MWB', 10, 3320),
    ('DISPATCH ON ENTERPRISE PLUS XS BLUEHDI 120 SS SWB', 10, 3327),
    ('DISPATCH ON ENTERPRISE PLUS XS BLUEHDI 150 SS SWB', 10, 3330),
    ('DISPATCH ON ENTERPRISE PLUS XS BLUEHDI 180 SS SWB', 10, 3333),
    ('DISPATCH ON ENTERPRISE XS BLUEHDI 115 SWB', 8, 2777),
    ('DISPATCH XL 1250 ENTERPRISE PLUS BLUEHDI SS EAT6', 8, 3220),
    ('DISPATCH XL 1400 ENTERPRISE PLUS BLUEHDI SS', 14, 2913),
    ('DISPATCH XS 1300 ENTERPRISE PLUS BLUEHDI SS EAT6', 8, 3223),
    ('DMAX ARCTIC TRUCKS DOUBLE CAB MWB MEDIUM ROOF', 22, 3066),
    ('DMAX ARCTIC TRUCKS EXTENDED CAB MWB MEDIUM RO', 11, 3067),
    ('DOBLO CARGO MAXI CRC SX MULTIJET COMFORTMATIC', 8, 3140),
    ('DOBLO CARGO SX MULTIJET COMFORTMATIC COMBI', 14, 2951),
    ('DS 4 HATCHBACK SPECIAL EDITION CROSSBACK TERRE ROUGE', 8, 3536),
    ('DUCATO ADRIA MATRIX PLATINUM COLLECTION M670SL', 6, 3189),
    ('DUCATO ADRIA MATRIX PLATINUM COLLECTION M670SL AUTO', 7, 3534),
    ('DUCATO ADRIA MATRIX SILVER COLLECTION M670SL', 6, 3040),
    ('DUCATO ADRIA SUN LIVING LIDO S 42 DF AUTO', 9, 2692),
    ('DUCATO BURSTNER IXEO TIME SOVEREIGN IT586 A', 7, 2956),
    ('DUCATO BURSTNER IXEO TIME SOVEREIGN IT745 A', 7, 2953),
    ('E', 15, 69),
    ('EXPERT ON PROFESSIONAL COMPACT BLUEHDI 115 SS SWB', 9, 3351),
    ('EXPERT ON PROFESSIONAL LONG BLUEHDI 120 SS MWB', 9, 3126),
    ('EXPERT ON PROFESSIONAL PLUS COMPACT BLUEHDI 150 S', 9, 3355),
    ('EXPERT ON PROFESSIONAL PLUS COMPACT BLUEHDI 180 S', 9, 3358),
    ('EXPERT ON PROFESSIONAL PLUS LONG BLUEHDI 120 SS M', 10, 3329),
    ('EXPERT ON PROFESSIONAL PLUS LONG BLUEHDI 150 SS M', 10, 3332),
    ('EXPERT ON PROFESSIONAL PLUS LONG BLUEHDI 180 SS M', 10, 3335),
    ('EXPERT ON PROFESSIONAL PLUS STANDARD BLUEHDI 120 M', 9, 3420),
    ('EXPERT ON PROFESSIONAL PLUS STANDARD BLUEHDI 120 S', 9, 3426),
    ('EXPERT ON PROFESSIONAL PLUS STANDARD BLUEHDI 150 S', 9, 3429),
    ('EXPERT ON PROFESSIONAL PLUS STANDARD BLUEHDI 180 S', 9, 3432),
    ('EXPERT ON PROFESSIONAL STANDARD BLUEHDI 115 SS MW', 9, 3353),
    ('F', 35, 70),
    ('FLHTCUSE3 SCREAMIN EAGLE ULTRA CLASSIC EGLIDE', 7, 3093),
    ('FOCUS 15 TDCI 120PS RED BLACK STLINE DCT', 18, 2562),
    ('FORFOUR NIGHT SKY BRABUS SPORT PREMIUM PLUS T', 16, 3202),
    ('FORFOUR ON NIGHT SKY BRABUS SPORT PREMIUM PLUS T', 20, 3391),
    ('FORFOUR ON NIGHT SKY BRABUS SPORT PREMIUM T', 18, 3035),
    ('FORFOUR ON NIGHT SKY PRIME SPORT PREMIUM PLUS', 18, 3209),
    ('FORFOUR ON NIGHT SKY PRIME SPORT PREMIUM PLUS T', 20, 3325),
    ('FREELANDER TD4 MASAI MARA STATION WAGON', 12, 2670),
    ('G', 95, 71),
    ('GALLARDO LP5704 SUPERLEGGERA EDIZIONE TECNICA', 7, 3078),
    ('GIULIETTA TBI QUADRIFOGLIO VERDE TCT LAUNCH EDTN', 7, 3348),
    ('GLACLASS 200 4MATIC SPORT PREMIUM CDI', 10, 2469),
    ('GLACLASS 21 GLA 220 D AMG SPORT 4MATIC DCT', 9, 2627),
    ('GLACLASS GLA 200 4MATIC AMG LINE PREMIUM', 8, 2606),
    ('GLACLASS GLA 220 D 4MATIC WHITEART PREMIUM PLUS', 8, 3139),
    ('GLACLASS GLA 250 4MATIC WHITEART PREMIUM PLUS', 7, 3042),
    ('GLACLASS GLA200 4MATIC AMG LINE PREMIUM PLUS', 8, 2930),
    ('GLACLASS GLA220 4MATIC AMG LINE PREMIUM PLUS', 16, 2932),
    ('GLACLASS GLA220 CDI 4MATIC AMG LINE PREMIUM PLUS', 8, 3172),
    ('GLACLASS GLA220 CDI 4MATIC SPORT PREMIUM PLUS', 7, 3039),
    ('GLACLASS ON 200 WHITEART PREMUIM PLUS D', 16, 2632),
    ('GLACLASS ON 220 4MATIC AMG LINE PREMIUM PLUS D', 11, 3009),
    ('GLECLASS GLE 350 4MATIC AMG LINE PREMIUM PLUS', 18, 2976),
    ('GLECLASS GLE 500 4MATIC AMG LINE PREMIUM PLUS', 9, 2973),
    ('GOLF S TSI BLUEMOTION TECHNOLOGY DSG', 6, 2531),
    ('GOLF SE NAVIGATION TSI BLUEMOTION TECHNOLOGY', 24, 3130),
    ('GOLF SE NAVIGATION TSI BLUEMOTION TECHNOLOGY DSG', 14, 3384),
    ('GRAND CHEROKEE 75TH ANNIVERSARY EDITION', 5, 2712),
    ('GRAND ESPACE DYNAMIQUE TOMTOM INITIALE LUXE PACK DCI', 8, 3590),
    ('GRAND SCENIC DYNAMIQUE TOMTOM BOSE ENERGY DCI 110', 11, 3299),
    ('GRAND SCENIC DYNAMIQUE TOMTOM BOSE ENERGY DCI 130', 11, 3301),
    ('GRAND SCENIC DYNAMIQUE TOMTOM BOSE ENERGY TCE 115', 11, 3316),
    ('GRAND SCENIC DYNAMIQUE TOMTOM BOSE ENERGY TCE 130', 11, 3313),
    ('GRAND SCENIC DYNAMIQUE TOMTOM BOSE PACK VVT', 7, 2998),
    ('GRAND SCENIC DYNAMIQUE TOMTOM ENERGY DCI 110', 10, 2970),
    ('GRAND SCENIC DYNAMIQUE TOMTOM ENERGY TCE 130', 10, 2984),
    ('GRAND SCENIC HYBRID ON DYNAMIQUE NAV DCI 110 ASSIST', 10, 3403),
    ('GRAND SCENIC HYBRID ON DYNAMIQUE S NAV DCI 110 ASSIST', 11, 3518),
    ('GRAND SCENIC HYBRID ON SIGNATURE NAV DCI 110 ASSIST', 10, 3408),
    ('GRAND TOURNEO CONNECT 15 CDTI 100PS TITANIUM POWERSHIFT DCT', 9, 4017),
    ('GRAND TOURNEO CONNECT 15 CDTI 100PS ZETEC POWERSHIFT DCT', 9, 3777),
    ('GRAND VOYAGER TOURING 25TH ANNIVERSARY', 8, 2694),
    ('H', 8, 72),
    ('I', 50, 73),
    ('I8 COUPE SPECIAL EDITION PROTRONIC SILVER', 6, 2879),
    ('INSIGNIA ON GRAND SPORT DESIGN NAV TURBO ECOTEC', 9, 3249),
    ('INSIGNIA ON GRAND SPORT DESIGN TURBO D', 16, 2621),
    ('INSIGNIA ON GRAND SPORT ELITE NAV TURBO D ECOTEC', 20, 3278),
    ('INSIGNIA ON GRAND SPORT SRI NAV TURBO D ECOTEC', 20, 3145),
    ('INSIGNIA ON GRAND SPORT SRI TURBO D ECOTEC', 18, 2884),
    ('INSIGNIA ON GRAND SPORT SRI VXLINE NAV TURBO', 9, 3080),
    ('INSIGNIA ON GRAND SPORT SRI VXLINE NAV TURBO D ECOT', 11, 3511),
    ('INSIGNIA ON GRAND SPORT TECH LINE NAV TURBO D', 40, 3060),
    ('INSIGNIA ON GRAND SPORT TECH LINE NAV TURBO D ECOTEC', 22, 3527),
    ('INSIGNIA ON SPORTS TOURER DESIGN NAV TURBO D ECOTEC', 20, 3549),
    ('INSIGNIA ON SPORTS TOURER DESIGN NAV TURBO ECOTEC', 9, 3449),
    ('INSIGNIA ON SPORTS TOURER DESIGN TURBO ECOTEC', 8, 3188),
    ('INSIGNIA ON SPORTS TOURER ELITE NAV TURBO D ECOTEC', 30, 3478),
    ('INSIGNIA ON SPORTS TOURER SRI NAV TURBO D', 36, 2878),
    ('INSIGNIA ON SPORTS TOURER SRI NAV TURBO D ECOTEC', 20, 3345),
    ('INSIGNIA ON SPORTS TOURER SRI VXLINE NAV TURBO', 9, 3280),
    ('INSIGNIA ON SPORTS TOURER SRI VXLINE NAV TURBO D EC', 11, 3548),
    ('INSIGNIA ON SPORTS TOURER TECH LINE NAV TURBO', 9, 3160),
    ('INSIGNIA ON SPORTS TOURER TECH LINE NAV TURBO D', 40, 3260),
    ('INSIGNIA ON SPORTS TOURER TECH LINE NAV TURBO D ECOT', 22, 3591),
    ('IS HYBRID ON 300H EXECUTIVE EDITION DUAL VVTI', 9, 3043),
    ('J', 1, 74),
    ('JUKE ACENTA PREMIUM COMFORT EXTERIOR DIGT 1', 18, 2975),
    ('JUKE ACENTA PREMIUM SAFETY EXTERIOR PURE DRI', 9, 3091),
    ('K', 22, 75),
    ('KANGOO MAXI LL21 BUSINESS PLUS ENERGY CRC DCI', 16, 3051),
    ('KANGOO ON MAXI LL21 CREW BUSINESS ENERGY DCI 110', 10, 3151),
    ('L', 135, 76),
    ('LAGUNA DYNAMIQUE TOMTOM INITIALE LUXE PACK DCI', 84, 3201),
    ('LAND CRUISER COLORADO LC4 8SEATS D4D', 6, 2406),
    ('LEON DIESEL HATCHBACK XCELLENCE TECHNOLOGY', 20, 2924),
    ('LEON DIESEL SPORT COUPE FR TITANIUM TECHNOLOGY', 28, 3255),
    ('LEON DIESEL SPORT TOURER SE DYNAMIC TECHNOLOGY', 21, 3254),
    ('LEON DIESEL SPORT TOURER XCELLENCE TECHNOLOGY', 24, 3212),
    ('M', 44, 77),
    ('M6', 24, 131),
    ('MASTER LL35 BUSINESS ENERGY DCI LR DCC TIPPER', 8, 3054),
    ('MASTER LLL35 BUSINESS ENERGY DCI LR DCC DRW', 8, 2899),
    ('MASTER LLL35 BUSINESS ENERGYDCI LRCC DROP DRW', 14, 3108),
    ('MASTER ML35 BUSINESS ENERGY DCI LR TIPPER DRW', 8, 3090),
    ('MASTER ML35 BUSINESS ENERGY DCI LRCC DROPSIDE', 14, 3089),
    ('MASTER MML45 BUSINESS ENERGY DCI SR PV DRW', 8, 2873),
    ('MASTER ON LHL35 BUSINESS ENERGY DCI 145 ELWB', 9, 2907),
    ('MASTER ON LML35 TW BUSINESS ENERGY DCI 145 ELWB', 20, 3115),
    ('MASTER ON SM35 BUSINESS ENERGY DCI 145 SWB', 9, 2781),
    ('MAZDA6 22 175PS PLATINUM SAFETY CRUISE P AUT', 9, 2914),
    ('MEGANE SCENIC THE ULTIMATE PRIVILEGE MONACO DCI', 7, 3228),
    ('MINI JOHN COOPER WORKS WORLD CHAMPIONSHIP 50', 20, 3050),
    ('MOVANO ON L1H1 F3300 COMBI SS CDTI ECOFLEX', 9, 2697),
    ('MULTISTRADA 950 STAR WHITE SILK TOURING PACK', 10, 3037),
    ('MX5 RF CONVERTIBLE SPECIAL EDITION LAUNCH', 6, 2839),
    ('N', 7, 78),
    ('NEW CODE VL 1600 KMM95INTRUDERMARAUDERBOU', 6, 2789),
    ('NT400 CABSTAR 3410 FREEZER REFRIDGERATED', 19, 2633),
    ('NT400 CABSTAR 3512 HIGH PAYLOAD SINGLE CAB DCI LWB', 12, 3176),
    ('NT400 CABSTAR 3512 HIGH PAYLOAD SINGLE CAB DCI MWB', 12, 3177),
    ('NT400 CABSTAR 3514 FREEZER SINGLE CAB DCI MWB', 11, 2868),
    ('NT400 CABSTAR ON 3513 DOUBLE DROPSIDE DCI LWB', 9, 2889),
    ('NT400 CABSTAR ON 3513 SINGLE DROPSIDE DCI LWB', 18, 2896),
    ('NT400 CABSTAR PRO FREEZER BOX 1127 PAYLOAD', 10, 2744),
    ('NV300 ON TEKNA L2H1 SS CREW DCI TWINTURBO', 18, 2707),
    ('P', 6, 80),
    ('PATHFINDER STATION WAGON MAMMOTH SPORTS ADVENTURE', 16, 3535),
    ('R', 105, 82),
    ('RANGE ROVER AUTOBIOGRAPHY BLACK SUPERCHARGED', 9, 3123),
    ('RANGE ROVER EVOQUE TD4 180HP AUTOBIOGRAPHY AUTO', 8, 3236),
    ('RANGE ROVER EVOQUE TD4 180HP HSE DYNAMI CONVER AUTO', 11, 3445),
    ('RANGE ROVER EVOQUE TD4 180HP HSE DYNAMI CONVERTIBLE', 10, 3468),
    ('RANGE ROVER EVOQUE TD4 180HP SE DYNAMIC CONVER AUTO', 11, 3440),
    ('RANGE ROVER EVOQUE TD4 180HP SE DYNAMIC CONVERTIBLE', 10, 3463),
    ('RANGE ROVER SPORT AUTOBIOGRAPHY SUPERCHARGED V8', 9, 3356),
    ('RANGE ROVER SPORT HYBRID ON HSE DYNAMIC SDV6', 10, 3034),
    ('RANGE ROVER V8 SVAUTOBIOGRAPHY DYNAMIC', 5, 2709),
    ('S', 368, 83),
    ('SANDERO STEPWAY DIESEL HATCHBACK LAUREATE', 5, 2875),
    ('SCENIC GRAND DYNAMIQUE TOMTOM BOSE PLUS EDC', 7, 2983),
    ('SCENIC GRAND DYNAMIQUE TOMTOM LUXE PACK DCI EDC', 8, 3207),
    ('SCENIC HYBRID ON DYNAMIQUE S NAV DCI 110 ASSIST', 10, 3122),
    ('SCENIC HYBRID ON SIGNATURE NAV DCI 110 ASSIST', 9, 3012),
    ('SCENIC XMOD DYNAMIQUE TOMTOM BOSE PLUS DCI EDC', 8, 3171),
    ('SCENIC XMOD DYNAMIQUE TOMTOM ENERGY TCE', 16, 2752),
    ('SCENIC XMOD DYNAMIQUETT BOSEPLUS ENERGY DCI', 14, 3049),
    ('SCLASS S300 L AMG LINE EXECUTIVE PREMIUM PLUS H', 12, 3157),
    ('SERIES 3 318D LUXURY GRAN TURISMO AUTO', 8, 2603),
    ('SERIES 320D EFFICIENTDYNAMICS BUSINESS TOURING', 36, 3229),
    ('SERIES 320D XDRIVE M SPORT GRAN TURISMO AUT', 20, 2944),
    ('SERIES 320I XDRIVE M SPORT GRAN TURISMO AUT', 30, 2949),
    ('SERIES 330D XDRIV M SPORT GRAN TURISMO', 10, 2610),
    ('SERIES 530I AUTHORITIES SMG TOURING', 6, 2444),
    ('SPACETOURER BLUEHDI 150HP SS XL BUSINESS', 7, 2766),
    ('SPRINTER AUTOSLEEPERS WINCHCOMBE AUTO', 4, 2699),
    ('SPRINTER LUNAR CRISTALL LANDSTAR RLS AUTO', 6, 2938),
    ('SPRINTER ON 311 BE CDI BLUEEFFICIENCY ELWB', 24, 2791),
    ('TALENTO ON SX 12 HR ECOJET 125 SS SWB HIGH ROOF', 12, 3030),
    ('TALENTO ON SX 12 HR ECOJET 145 SS LWB HIGH ROOF', 12, 3025),
    ('TIGUAN SPORT 4MOTION BLUEMOTION TECHNOLOGY', 18, 3044),
    ('TOURNEO CONNECT 15 CDTI 100PS TITANIUM POWERSHIFT DCT', 8, 3621),
    ('TOURNEO CONNECT 15 CDTI 100PS ZETEC POWERSHIFT DCT', 8, 3381),
    ('TRAFIC LL29 BUSINESS PLUS ENERGY DCI CREW', 14, 2807),
    ('TRAFIC ON LL29 SPORT NAV CREW ENERGY DCI 145 SWB', 11, 3143),
    ('TRAFIC ON SL29 SPORT NAV CREW ENERGY DCI 145 SWB', 11, 3150),
    ('TRAFIC SL29 BUSINESS PLUS ENERGY DCI LR CRC', 16, 2915),
    ('TRANSIT CUSTOM 290 L1 DIESEL FWD SPECIAL EDITIONS', 9, 3311),
    ('TRANSIT CUSTOM ON 290 L1H1 LIMITED TDCI SWB', 18, 2854),
    ('TRANSIT CUSTOM ON 290 L1H1 TREND COLOUR EDITION TDCI SWB', 11, 3771),
    ('TRANSPORTER ON T30 KOMBI HIGHLINE BMT SWB', 18, 2817),
    ('TRANSPORTER ON T30 KOMBI STARTLINE BMT SWB', 18, 2927),
    ('TRANSPORTER ON T32 HIGHLINE 4MOTION BMT LWB', 30, 2964),
    ('TRANSPORTER ON T32 KOMBI HIGHLINE 4MOTION BMT LWB 4', 11, 3450),
    ('TRANSPORTER ON T32 KOMBI HIGHLINE BLUEMOTION TECHNOLOGY', 8, 3886),
    ('TRANSPORTER ON T32 KOMBI SPORTLINE BITDI BLUEMOTION TEC', 45, 3858),
    ('TRANSPORTER ON T32 KOMBI STARTLINE 4MOTION BMT LWB', 10, 3476),
    ('TRANSPORTER ON T32 KOMBI STARTLINE 4MOTION BMT SWB', 10, 3483),
    ('TRANSPORTER ON T32 KOMBI STARTLINE BMT LWB', 18, 2922),
    ('TRANSPORTER ON T32 KOMBI STARTLINE BMT SWB', 18, 2929),
    ('TRANSPORTER ON T32 KOMBI TRENDLINE 4MOTION BMT LWB', 10, 3459),
    ('TRANSPORTER ON T32 KOMBI TRENDLINE 4MOTION BMT SWB', 10, 3466),
    ('TRANSPORTER ON T32 KOMBI TRENDLINE BMT LWB', 18, 2905),
    ('TRANSPORTER ON T32 TRENDLINE 4MOTION BMT LWB', 10, 3057),
    ('TRANSPORTER T32 KOMBI SPORTLINE 60 BITDI LWB', 20, 3014),
    ('TRAVELLER BUSINESSSTANDARD BLUEHDI115 SS', 4, 2824),
    ('TRAVELLER ON ACTIVE STANDARD BLUEHDI 115 SS', 8, 2901),
    ('TRAVELLER ON ALLURE COMPACT BLUEHDI 180 SS', 8, 2838),
    ('TRAVELLER ON ALLURE STANDARD BLUEHDI 150 SS', 8, 2909),
    ('TRAVELLER ON BUSINESS COMPACT BLUEHDI 115 SS', 16, 3003),
    ('TRAVELLER ON BUSINESS STANDARD BLUEHDI 150 SS', 16, 3076),
    ('TRAVELLER ON BUSINESS STANDARD BLUEHDI 95 SS', 16, 3036),
    ('TRAVELLER ON BUSINESS VIP STANDARD BLUEHDI 180 SS', 9, 3350),
    ('U', 7, 85),
    ('V', 10, 86),
    ('VESPA GTSGTV 300 IE SUPERVIA MONTENAPOLEONE', 6, 2994),
    ('VITO 116 COMPACT TRAVELINE CDI MWB MEDIUM ROO', 11, 3026),
    ('VITO TOURER ON 114 SELECT COMPACT BLUETEC SWB', 9, 3053),
    ('VITO TOURER ON 114 SELECT LONG BLUETEC LWB', 9, 2831),
    ('VITO TOURER ON 116 SELECT X LONG BLUETEC ELWB', 10, 3022),
    ('VIVARO 2900 L1H1 CDTI DCB SPORTIVE ECOFLEX', 56, 2759),
    ('VIVARO L1H1 2700 LIMITED EDITION NAV CDTI PV BITURBO SS', 20, 3638),
    ('VIVARO L1H1 2900 LIMITED EDITION NAV CDTI DCB BITURBO SS', 20, 3675),
    ('VIVARO L2H1 2900 LIMITED EDITION NAV CDTI DCB BITURBO SS', 20, 3676),
    ('VIVARO L2H1 2900 LIMITED EDITION NAV CDTI PV BITURBO SS', 20, 3641),
    ('VIVARO ON 2700 L1H1 CDTI BITURBO SS BLUEINJECTION', 9, 3263),
    ('VIVARO ON 2700 L1H1 LIMITED EDITION NAV CDTI BITUR', 20, 3286),
    ('VIVARO ON 2700 L1H1 SPORTIVE CDTI BITURBO SS BLUE', 20, 3256),
    ('VIVARO ON 2700 L1H1 SPORTIVE CDTI BLUEINJECTION', 8, 3166),
    ('VIVARO ON 2900 L1H1 CDTI BITURBO SS BLUEINJECTION', 36, 3265),
    ('VIVARO ON 2900 L1H1 SPORTIVE CDTI BITURBO SS BLUE', 40, 3258),
    ('VIVARO ON 2900 L1H1 SPORTIVE CDTI BLUEINJECTION', 16, 3168),
    ('VIVARO ON 2900 L2H1 LIMITED EDITION NAV CDTI BITUR', 30, 3289),
    ('VIVARO ON 2900 L2H1 SPORTIVE CDTI BITURBO SS BLUE', 40, 3259),
    ('VIVARO ON 2900 L2H1 SPORTIVE CDTI BLUEINJECTION', 16, 3169),
    ('X', 14, 88),
    ('XL 1200C SPORTSTER 1200 CUSTOM A 110TH ANIVERSARY', 9, 3185),
    ('XL 1200C SPORTSTER 1200 CUSTOM B 110TH ANIVERSARY', 9, 3186),
    ('XTRAIL ARCTIX EXPEDITION SPORTS ADVENTURE', 10, 2993),
    ('XTRAIL STATION WAGON ARCTIX EXPEDITION SPORT A', 18, 3279),
    ('XTRAIL STATION WAGON SPORT EXPEDITION EXTREME', 27, 3261),
    ('Z', 30, 90),
    ('Z SERIES SDRIVE30I M SPORT HIGHLINE EDITION', 20, 2967)
;

Open in new window

0
PortletPaulEE Topic AdvisorCommented:
perhaps something less exotic?
select StrShort, min(asciisum) minascii, max(asciisum) maxascii, count(distinct TotalClientCodes) CountClientCodes
from TblWords
group by  StrShort
having count(distinct TotalClientCodes) > 1

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ive attached the output from ID: 42050836 too I wonder if a better way to go is to find the rows where it has the same words but in different order in without using the ascii sum.
The results are the ones you expected or not?
If not, what's wrong or missing?
If yes, what's really the issue now?
0
PeterBaileyUkAuthor Commented:
Apologies for delay was taken ill the last couple of days and will be having surgery shortly..just reading the comments now.
0
PeterBaileyUkAuthor Commented:
thank you and once again apologies for slow response
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.