Logic

Question

Post reply

Logic

C_Singh

Valued Member

Points: 50
More actions
August 2, 2011 at 2:14 am

#97913

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 1

Vijay

Does your database have a case-sensitive collation?

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

-- 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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

C_Singh Valued Member Points: 50 More actions · Answer 3

Hi Jhon,

My database is case insensitive and collation is SQL_Latin1_General_CP1_CI_AS.

Thanks,

Vijay

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 4

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

C_Singh Valued Member Points: 50 More actions · Answer 5

C_Singh

Valued Member

Points: 50

August 2, 2011 at 3:57 am

#1362016

Great.. thanks allot guys:-)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Nice work John, cheers 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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