August 2, 2011 at 2:14 am
Hi Experts,
Please help me solving below issue.
I have a string called 'IncomeTax'. I want to add 1 space wherever upper case alphabets exists before upper case alphabet. In my case its 'T'. I want to display string as 'Income Tax'. But it should happen dynamically for all the strings.
Please advice.
Thanks,
Vijay
August 2, 2011 at 2:29 am
Vijay
Does your database have a case-sensitive collation?
John
August 2, 2011 at 3:33 am
-- make some sample data
DROP TABLE #Test
CREATE TABLE #Test (MyString VARCHAR(30))
INSERT INTO #Test (MyString) SELECT 'IncomeTax'
INSERT INTO #Test (MyString) SELECT 'InheritanceTax'
INSERT INTO #Test (MyString) SELECT 'PurchaseTax'
INSERT INTO #Test (MyString) SELECT 'WorkingFamilyTaxCredit'
-- here's a simple solution which will put a space before the first three capitalised letters,
-- excluding the first letter of the string
-- note the use of COLLATE - you may have to experiment depending upon the setting of your db
SELECT t.MyString,
NewString = CASE
WHEN p3 IS NOT NULL THEN STUFF(STUFF(STUFF(t.MyString, a.p3, 0, ' '), a.p2, 0, ' '), a.p1, 0, ' ')
WHEN p2 IS NOT NULL THEN STUFF(STUFF(t.MyString, a.p2, 0, ' '), a.p1, 0, ' ')
WHEN p1 IS NOT NULL THEN STUFF(t.MyString, a.p1, 0, ' ')
ELSE t.MyString END
FROM #Test t
CROSS APPLY( -- collect the positions of the first three capitalised letters after the first one
SELECT
p1 = MAX(CASE WHEN pos = 1 THEN d.rn END),
p2 = MAX(CASE WHEN pos = 2 THEN d.rn END),
p3 = MAX(CASE WHEN pos = 3 THEN d.rn END)
FROM (
SELECT n.rn, pos = ROW_NUMBER() OVER(ORDER BY rn)
FROM (SELECT TOP 30 rn = ROW_NUMBER() OVER (ORDER BY NAME)+1 FROM sys.COLUMNS) n
WHERE SUBSTRING(t.MyString, n.rn,1) COLLATE SQL_Latin1_General_CP1_CS_AS = UPPER(SUBSTRING(t.MyString, n.rn,1))
AND n.rn < LEN(t.MyString)
AND NOT SUBSTRING(t.MyString, n.rn-1,1) = ' '
) d
) a
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 2, 2011 at 3:36 am
Hi Jhon,
My database is case insensitive and collation is SQL_Latin1_General_CP1_CI_AS.
Thanks,
Vijay
August 2, 2011 at 3:54 am
I've taken the liberty of tweaking so that it doesn't get fooled by non-alpha characters.
Code is posted as an attachment since it timed out when I tried to post in-line.
John
August 2, 2011 at 3:57 am
Great.. thanks allot guys:-)
August 2, 2011 at 4:04 am
Nice work John, cheers 🙂
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply