StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Assuming your data is already in a table, does it have to be using a reg ex? Why don't you create a scalar function to do this and follow the steps below:</p> <ol> <li>UPDATE tableName SET stringa = REPLACE(stringa,'_',' ') --Replace underscores with a space</li> <li>UPDATE tableName SET stringa = dbo.ProperCase(stringa) --update the field using the function I have created below</li> <li>UPDATE tableName SET stringa = REPLACE(stringa,' ','') --Now remove the spaces</li> </ol> <h2>FUNCTION</h2> <pre><code>CREATE FUNCTION [dbo].[ProperCase] ( @String VARCHAR(255) ) RETURNS VARCHAR(255) AS BEGIN DECLARE @i INT DECLARE @Char CHAR(1) DECLARE @CorChar CHAR(1) DECLARE @PrevAscii INT DECLARE @PrevAscii2 INT DECLARE @Ret VARCHAR(255) /* Captalisation rules */ -- Capitalise first letter of each word -- Capitalise next letter after special characters -- eg joe o'bloggs-bloggs jr -> Joe O'Bloggs-Bloggs Jr SET @Ret = '' SET @i = 1 WHILE @i <= LEN(@String) BEGIN SET @Char = SUBSTRING(@String, @i, 1) SET @CorChar = CASE WHEN @i = 1 THEN UPPER(@Char)-- First letter WHEN @PrevAscii = 32 THEN UPPER(@Char)-- Follows Space WHEN @PrevAscii = 39 AND @PrevAscii2 = 79 THEN UPPER(@Char)-- Follows O' WHEN @PrevAscii = 45 THEN UPPER(@Char)-- Follows Dash WHEN @PrevAscii = 46 THEN UPPER(@Char)-- Follows Fullstop ELSE LOWER(@Char) END SET @Ret = @Ret + @CorChar SET @i = @i + 1 SET @PrevAscii2 = @PrevAscii SET @PrevAscii = ASCII(@CorChar) END --Now sort out capitalistaion for van, de, den, and der SET @Ret = CASE WHEN @Ret LIKE 'Van %' THEN 'v' + SUBSTRING(@Ret,2,255) ELSE @Ret END SET @Ret = CASE WHEN @Ret LIKE 'De %' THEN 'd' + SUBSTRING(@Ret,2,255) ELSE @Ret END SET @Ret = CASE WHEN @Ret LIKE 'Der %' THEN 'd' + SUBSTRING(@Ret,2,255) ELSE @Ret END SET @Ret = CASE WHEN @Ret LIKE 'Den %' THEN 'd' + SUBSTRING(@Ret,2,255) ELSE @Ret END SET @Ret = REPLACE(@Ret,' De ',' de ') SET @Ret = REPLACE(@Ret,' Der ',' der ') SET @Ret = REPLACE(@Ret,' Den ',' den ') RETURN @Ret END </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload