Make Capital after ( - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Make Capital after (

Author

Topic

dmcbrier
Starting Member

5 Posts

Posted - 2015-03-23 : 07:02:02

Is there a more efficient way to write this T-SQL script
Maybe using Regular Expression and UPPER

-- Make Capital after (
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(a','(A');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(b','(B');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(c','(C');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(d','(D');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(e','(E');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(f','(F');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(g','(G');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(h','(H');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(i','(I');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(j','(J');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(k','(K');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(l','(L');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(m','(M');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(n','(N');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(o','(O');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(p','(P');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(q','(Q');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(r','(R');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(s','(S');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(t','(T');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(u','(U');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(v','(V');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(w','(W');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(x','(X');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(y','(Y');
UPDATE Contact_Company2 SET Street = REPLACE(Street,'(z','(Z')

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-23 : 08:50:23

SQL does not have builtin regular expressions (though you can no doubt download or write a CLR that will do it) but you don't need 26 UPDATES. Instead you can compose the expressions:


...REPLACE(REPLACE(REPLACE Street,'(a', '(A'), '(b','(B'....)))

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-03-23 : 10:41:16

I would do the updates using the nested REPLACE statements that gbritton suggested as well, but if you wanted to get fancy, here is an example.

CREATE TABLE #tmp(St VARCHAR(32));
INSERT INTO #tmp VALUES ('(abcdef'), ('(xyze');
INSERT INTO #tmp VALUES ('xx(def'), ('yzz(');

UPDATE #tmp SET st = 
	STUFF(
		st,
		PATINDEX('%([a-z]%',st)+1,
		1,
		UPPER(SUBSTRING(st, PATINDEX('%([a-z]%',st)+1, 1))) FROM #tmp
WHERE
	st LIKE '%([a-z]%'
	
SELECT * FROM #tmp;

As you can see it is less readable, and I wouldn't know what I was trying to do if I looked at this two weeks later even though I created this monster.

Kristen
Test

22859 Posts

Posted - 2015-03-24 : 10:34:56

Whichever method you use you should definitely put a WHERE clause on the UPDATE so you only select rows that will actually be changed, otherwise you will generate huge logs for records that have been updated, but have no physical change.

If your database is case INsensitive then make sure you COLLATE the WHERE Clause test to BINARY so that only lower case matches qualify.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-03-24 : 19:51:34

IF.... You can assume an ASCII character set... (Yes! It's a complete hack and Yes! My assembly language roots are showing...)[CODE]declare @tbl table (
stng varchar(100)
)

insert into @Tbl(stng)
values
('CHANGE:(s .....'),
('NO CHANGE: No Paren'),
('NO CHANGE: PAREN AT END ('),
('NO CHANGE: ( no letter afterwards'),
('?? CHANGE: (A but who could tell?');

;with ParenIndex
as (
select
stng,
patindex('%([a-z]%', stng) idx
from
@Tbl
)
select
case
when idx = 0 then stng -- No paren or paren as last character
else replace(
stng,
'(' + substring(stng, idx + 1, 1),
'(' + char(ascii(substring(stng, idx + 1, 1)) & 0xDF)
)
end
from
ParenIndex[/CODE]One REPLACE function call; Only called when the pattern exists.

I would rather be the man who bought the Brooklyn Bridge than the one who sold it. -Will Rogers

dmcbrier
Starting Member

5 Posts

Posted - 2015-03-25 : 16:08:16

Based on what I was doing this made the most sense. Some of the ideas above are incredible!!! Thank you very much.
Getting everything into 1 update statement makes lots of sense.

-- Make Street Capital after (
UPDATE Contact_Company2 SET Street =
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Street,
'(a', '(A'), '(b', '(B'), '(c', '(C'), '(d', '(D'), '(e', '(E'), '(f', '(F'), '(g', '(G'), '(h', '(H'), '(i', '(I'), '(j', '(J'), '(k', '(K'), '(l', '(L'), '(m', '(M'), '(n', '(N'), '(o', '(O'), '(p', '(P'), '(q', '(Q'), '(r', '(R'), '(s', '(S'), '(t', '(T'), '(u', '(U'), '(v', '(V'), '(w', '(W'), '(x', '(X'), '(y', '(Y'), '(z', '(Z')

Kristen
Test

22859 Posts

Posted - 2015-03-26 : 22:03:13

You don't have a WHERE clause to only update rows that need it ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources