regular expression - change to camel case / remove underscore

Question

I want to convert all the strings in my notepad/Sql Server management studio into camel case and remove all the underscores.

E.g.:

string a = REFERENCE_DATA_ID
String b = ReferenceDataId

I want to remove the underscore in string a and covert into a camel case as shown in string b. Please help me out.

Answer 1

You can do this with one step in notepad++:

Find: ([a-z]+)[_]?([a-z]?)([a-z]+)[_]?([a-z]?)([a-z]+)[_]?([a-z]?)([a-z]+)\.php
Replace: $1\U$2\L$3\U$4\L$5\U$6\L$7

The only issue with this, is that you need to know the max time the under score can be present and how the string ends. In the above example, i'm replacing php file names to camelCase, knowing that the under score cannot be present more than 3 times, less is no problem.

Answer 2

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:

1. UPDATE tableName SET stringa = REPLACE(stringa,'_',' ') --Replace underscores with a space
2. UPDATE tableName SET stringa = dbo.ProperCase(stringa) --update the field using the function I have created below
3. UPDATE tableName SET stringa = REPLACE(stringa,' ','') --Now remove the spaces

FUNCTION

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