Subscribe to this thread
Home - General / All posts - SQL for Make Title Case
anajera124 post(s)
#15-Jan-15 19:31

Hello

How can I convert a column that is in upper case to Title Case? I am looking for the same approach as the Make Title Case in the transform tool bar but using SQL. I have searched the SQL reference but only found funtions for upper/lower case transformations.

Thanks in advance for your help


BiciMapas Mexico GPS Maps & GIS Data www.bicimapas.com.mx

tjhb
8,059 post(s)
#15-Jan-15 21:18

I hoped that this regular expression would do it, but apparently the LCase() function is not applied prior to the substitution, so the source string winds up unchanged. Someone might know why.

SELECT RegExp([UPPER CASE]"\B([A-Z]+)\b", LCase("$1")) AS [Title Case]

FROM [Table]

[Edit.] Sorry, that was completely brainless of me. LCase("$1") is of course applied to the replacement string "$1", before it is passed to RegExp() as an argument. LCase() is not somehow magically passed to RegExp() as a function.

tjhb
8,059 post(s)
#15-Jan-15 22:17

I think you will need a script for this.

anajera124 post(s)
#15-Jan-15 23:15

You may be right. I tried the SQL code you provided without results. Any ideas on how to do it via script (VBscript would be preferred)?

Thank you


BiciMapas Mexico GPS Maps & GIS Data www.bicimapas.com.mx

tjhb
8,059 post(s)
#16-Jan-15 03:28

Quick and dirty. Makes assumptions, mainly that the column contains strings composed of words, separated by single spaces.

Make a backup before you run the script.

Adjust the strings in lines 2 and 3 to the correct names.

Sub Main

    tblname = "Table" ' name of target table

    colname = "Text" ' name of target column

    Set tbl = Document.ComponentSet(tblname)

    Set re = New RegExp

    re.Global = True

    re.IgnoreCase = False

    re.Pattern = "\b\w+\b"

    For Each rec In tbl.RecordSet

        newtext = ""

        Set matches = re.Execute(rec.Data(colname))

        For Each match In matches

            newtext = newtext & UCase(Left(match.Value, 1)) & LCase(Mid(match.Value, 2))

            If newtext <> "" Then

                newtext = newtext & " "

            End If

        Next ' match

        Application.History.Log newtext & vbCrLf, True

        rec.Data(colname) = newtext

    Next ' rec

End Sub

jkelly


1,234 post(s)
#16-Jan-15 04:52

While you ask for vbscript, this (and many other things) are just so much easier leveraging the .NET framework. Below is the problem solved using the rather handy TextInfo object which is meant for exactly this.

By the way, you can safely ignore the Log class, the new Log() statement and the log.Debug statement should you wish, I just find this a useful helper.

//C#

using Manifold.Interop.Scripts;

using Manifold.Interop;

using System;

using System.Globalization;

class Script {

 static void Main() {

 Application app = Context.Application;

 Document doc = (Document)app.ActiveDocument;

 ComponentSet comps = (ComponentSet)doc.ComponentSet;

 Log log = new Log();

 //select your drawing here...

 Drawing drawing = (Drawing)comps["Drawing"];

 Table table = (Table)drawing.OwnedTable;

 RecordSet records = table.RecordSet;

 

 //create a textInfo object with whatever culture you wish

 //I've defaulted to mexico but if it's English, en-US is a good

 //start

  TextInfo textInfo = new CultureInfo("es-MX",false).TextInfo;

 

 foreach(Record record in records)

 {

 string text = (string)record.get_Data("Description");

 //TitleCase the string

 string titleCased = textInfo.ToTitleCase(text);

 //write it back to table

 record.set_Data("Description", titleCased); 

 

 }

 log.Debug("Title Casing Completed");

 

 }

}

public class Log {

 private Application app; 

 private History logger;

 public Log()

 {

 app = Context.Application;

 logger = app.History;

 }

 public void Debug(string text)

 {

 DateTime current = DateTime.Now;

 logger.Log("Debug " + current.ToString() + ":  " + text + "\n",null); 

 }

}

Attachments:
TitleCasing.map


James Kelly

http://www.locationsolve.com

anajera124 post(s)
#14-Apr-15 23:05

Hello,

My apologies for not replying earlier, I lost track of this post. I will give both options a try.

Thank you


BiciMapas Mexico GPS Maps & GIS Data www.bicimapas.com.mx

anajera124 post(s)
#14-Apr-15 23:20

Thank you TJHB and JKELLY. Both scripts get the results we were looking for.

Again, my apologies for the delay.


BiciMapas Mexico GPS Maps & GIS Data www.bicimapas.com.mx

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.