Add space for capital letter in a string

General
Enthusiast

Add space for capital letter in a string

Hi,

 

First name for some associates is coming as  WillSmith but client wants the output as Will Smith so we need to add space based on capital letter in a string.

 

Current Output

WillSmith

 

Expected Output

Will Smith

 

Please  Suggest .

 

7 REPLIES
Junior Contributor

Re: Add space for capital letter in a string

Use a Regular Expression:

regexp_replace(Name, 
   '([A-Z])', -- only Latin A-Z, i.e. excludes diacritics, etc. ('(\p{Lu})' doesn't work)   
   ' \1', -- add a space before the found character
   2, -- start to search at the 2nd character
   1) -- only replace the 1st occurence

When you remove the last parameter 'MartyMcFly' will be changed to 'Marty Mc Fly'

 

Enthusiast

Re: Add space for capital letter in a string

 

Hi Dnoeth,

 

I have given as REGEXP_REPLACE('WillSmith','([A-Z])',' \1',2,1) but it is giving below output.

 

OUTPUT :

Will \1mith

 

Please let me know if i have used it correctly.

Junior Contributor

Re: Add space for capital letter in a string

Ops, what's your Teradata release?

 

This works fine on 15 & 15.10.

Enthusiast

Re: Add space for capital letter in a string

We use Teradata 14.

Enthusiast

Re: Add space for capital letter in a string

 

Hi,


I checked again and we are using Teradata 15.00.0.04

Please let me know what i am doing wrong.

 

Currently i am using CASE with SUBSTR & CHAR2HEXINT (mentioned below only part of complete query) but it is very code big code to accomplish this.

 

CASE WHEN (SUBSTR(COLUMN),2,1) BETWEEN CHAR2HEXINT('A') AND CHAR2HEXINT('Z')

THEN.....

 

Please suggest.

 

 

Junior Contributor

Re: Add space for capital letter in a string

15.00.0.04 can't be your Teradata version unless it's an old TD Express (but there's a newer available).

 

select InfoData from dbc.dbcinfoV where InfoKey = 'RELEASE'

Instead of your CHAR2HEXINT approach (btw, the code you showed will never work) you can try a SUBSTR/REGEXP_INSTR combination:

case
   when regexp_instr(col,'([A-Z])', 2) = 0
   then col
   else substr(col, 1, regexp_instr(col,'([A-Z])', 2)-1) || ' ' ||
        substr(col,    regexp_instr(col,'([A-Z])', 2))
end

 

Enthusiast

Re: Add space for capital letter in a string

 

Thank you very much dnoeth for your help. It is working now.

 

Could you please tell me where can i get complete documentation related to REGEXP functions.