I should have been clearer with my problem definition!
The rules are as follows:
I have a string = 'A B C D EFG ProperWord QR S T'
I want to generate a comma delimited 3 part string with the following rules:
1) Part1 = Concatenate all single chacaters in the string until you reach part of string where two or more characters are joined
So based on the above string Part1 would be ABCD
2) Part2 = EFG
3) Part3 = ProperWord
This is some of the code I'be writing but I just can't get this to work well!
ALTER FUNCTION dbo.fn_NormaliseOrgName(@OrgName NVARCHAR(200))
RETURNS NVARCHAR(200)
AS
BEGIN
declare @OrgName NVARCHAR(200) = 'RA M I R A DI PETTI C & C SAS'
DECLARE @Part1 NVARCHAR(200) = ''
DECLARE @Part2 NVARCHAR(200) = ''
DECLARE @Part3 NVARCHAR(200) = ''
IF PATINDEX('%[A-Z][A-Z]%', @OrgName) > 1
BEGIN
SET @Part1 = LEFT(@OrgName, PATINDEX('%[A-Z][A-Z]%', @OrgName)-1)
IF LTRIM(RTRIM(REPLACE(@OrgName, @Part1, '') = ''
BEGIN
RETURN @Part1 + ',' + @Part2 + ',' + @Part3
END
ELSE
select @Part1 = case when PATINDEX('%[A-Z][A-Z]%', @OrgName) > 1
then left(@OrgName, PATINDEX('%[A-Z][A-Z]%', @OrgName)-1)
else LEFT(@OrgName, patindex('%[ ]%', @OrgName + ' ')-1) end
select @part1
select @Part2 = substring(@OrgName, len(@Part1)+1 , LEN(@OrgName))
select @Part2
--select @Part1
select @Part2 = left( ltrim(substring(@OrgName, len(@Part1)+1 , LEN(@OrgName))), charindex(' ', ltrim(substring(@OrgName, len(@Part1)+1 , LEN(@OrgName))))-1)
select @Part2
--RETURN @Part1
RETURN @Part2
--RETURN @Part3
END
select dbo.fn_NormaliseOrgName('R A M I R A DI PETTI C & C SAS')
---------------------------------------------------------
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