August 30, 2013 at 7:50 am
Hi all, hope in your help.
I try this sql query:
SELECT
[NAMES], [NUMBER]
FROM
[CV].[dbo].[T40]
WHERE
[NUMBER] = '44644'
GROUP BY
[NAMES], [NUMBER];
the output is:
NAMESNUMBER
BENCORE S.R.L.44644
BENCORES.R.L. 44644
I need instead this other output, can you help me?
NAMESNUMBER
BENCORE S.R.L.44644
August 30, 2013 at 7:56 am
Can you update the Names column in your T40 table?
UPDATE T40
SET Names = REPLACE(Names, 'S.R.L.', ' S.R.L.')
WHERE Names LIKE '%[A-Z]S.R.L.%'
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
August 30, 2013 at 7:59 am
thank you for reply but I've in T40 one millions of records ... :ermm:
August 30, 2013 at 8:04 am
Well then your other option is to include this in a case statement in your SELECT
Something like this:
SELECT
CASE WHEN [NAMES] like '%[A-Z]S.R.L.%' THEN REPLACE(Names, 'S.R.L.', ' S.R.L.') ELSE [NAMES] END, [NUMBER]
FROM
[CV].[dbo].[T40]
WHERE
[NUMBER] = '44644'
GROUP BY
CASE WHEN [NAMES] like '%[A-Z]S.R.L.%' THEN REPLACE(Names, 'S.R.L.', ' S.R.L.') ELSE [NAMES] END, [NUMBER];
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
August 30, 2013 at 3:45 pm
If this is a more general thing than just the one case, maybe something like
SELECT
MIN([NAMES]) AS [NAMES], [NUMBER]
FROM
( SELECT [CV].[dbo].[T40]
WHERE
[NUMBER] = '44644'
) R
GROUP BY
[NAMES], [NUMBER];
which will work when you substitute another number for 44644 whether or not the rows containing that number have a similar problem. (MIN works because space sorts before any alphabetical character).
But it would be better to look and see what all the problems are so that you can be sure that you deal with them all
select [NAMES],[NUMBER] from T40
where [NUMBER] IN (
select [NUMBER] from (
select COUNT(*) AS CNT, [NUMBER]
from T40 group by [NUMBER]
)
order by [NUMBER],[NAMES]
will give you a list of all numbers that have more than one name, and the corresponding names. You may find you need something a bit more complicated than the simple use of MIN to correct a missing space if there are several different sorts of typing errors in the names.
Tom
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply