{"id":303,"date":"2020-03-25T18:26:18","date_gmt":"2020-03-25T18:26:18","guid":{"rendered":"http:\/\/eipsoftware.com\/musings\/?p=303"},"modified":"2021-09-27T00:55:42","modified_gmt":"2021-09-27T00:55:42","slug":"add-a-space-before-upper-case-letter","status":"publish","type":"post","link":"https:\/\/eipsoftware.com\/musings\/add-a-space-before-upper-case-letter\/","title":{"rendered":"Add a Space Before Upper Case Letter"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\"><em>\u201cWhy doesn&#8217;t this function exist?\u201d<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We have all had the situation where there is a need for a little custom function to modify strings or do simple math on a column.  Today I am going to show how to create a User Defined Function (UDF) in Microsoft SQL.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p class=\"wp-block-paragraph\">The function will allow you to put a space before Upper case (capital), letters. The function will read the input string character by character and insert spaces before a capital letter.  Additionally the function provides the option to not a space between consecutive upper case letters, i.e SQL.  <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"font:courier-new width-set:true width:900 lang:tsql range:1-1000 decode:true \" title=\"Function Space Before Capital Letters\">CREATE FUNCTION\t\tdbo.SpaceBeforeCapital \n\t\t\t\t\t(\n\t\t\t\t\t  @InputString NVARCHAR(MAX)\n\t\t\t\t\t, @PreserveAdjacentCaps BIT\n\t\t\t\t\t)\nRETURNS NVARCHAR(MAX)\n\nAS\nBEGIN\n\n\tDECLARE\t\t@i\t\t\t\tINT\n\t\t\t\t, @j\t\t\tINT\n\t\t\t\t, @previous\t\tNCHAR\n\t\t\t\t, @current\t\tNCHAR\n\t\t\t\t, @next\t\t\tNCHAR\n\t\t\t\t, @result\t\tNVARCHAR(MAX)\n\n\tSELECT\n\t\t\t\t@i = 1\n\t\t\t\t, @j = LEN(@InputString)\n\t\t\t\t, @result = ''\n\n\n\tWHILE @i &lt;= @j\n\tBEGIN\n\t\t\tSELECT\n\t\t\t\t\t@previous = SUBSTRING(@InputString,@i-1,1)\n\t\t\t\t\t, @current = SUBSTRING(@InputString,@i+0,1)\n\t\t\t\t\t, @next = SUBSTRING(@InputString,@i+1,1)\n\n\n\t\t\tIF @current = UPPER(@current) COLLATE Latin1_General_CS_AS\n\t\t\t\tBEGIN\n\t\t\t\t-- Add space if Current is UPPER \n\t\t\t\t-- and either Previous or Next is lower or user chose not to preserve adjacent caps\n\t\t\t\t-- and Previous or Current is not already a space\n\t\t\t\t\tIF @current = UPPER(@current) COLLATE Latin1_General_CS_AS\n\t\t\t\t\t\tAND (\n\t\t\t\t\t\t\t\t@previous &lt;&gt; UPPER(@previous) COLLATE Latin1_General_CS_AS\n\t\t\t\t\t\t\t\tOR  @next &lt;&gt; UPPER(@next) collate Latin1_General_CS_AS\n\t\t\t\t\t\t\t\tOR  @PreserveAdjacentCaps = 0\n\t\t\t\t\t\t)\n\t\t\t\t\t\tAND @previous &lt;&gt; ' '\n\t\t\t\t\t\tAND @current &lt;&gt; ' '\n\t\t\t\t\n\t\t\t\t\tSET @result = @result + ' '\n\t\t\t\tEND \n\n\t\tSET @result = @result + @current\n\t\tSET @i = @i + 1\n\tEND \n\n\tRETURN @result\nEND<\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">And the way to call the function is via a SELECT statement is as follows<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"width-set:true width:900 lang:tsql range:1-100 decode:true \" title=\"Function Space Before Capital Letters\">-- preserve adjacent upper case letters\nSELECT dbo.SpaceBeforeCap('ThisIsASampleDBString', 1) \n\n-- spaces between adjacent upper case letters\nSELECT dbo.SpaceBeforeCap('ThisIsASampleDBString', 0) \n\n-- preserve adjacent upper case letters\nSELECT dbo.SpaceBeforeCap([YourFieldName], 1) \nFROM dbo.YourTableName\n;\n<\/pre><\/div>\n","protected":false},"excerpt":{"rendered":"<p>\u201cWhy doesn&#8217;t this function exist?\u201d We have all had the situation where there is a need for a little custom function to modify strings or do simple math on a column. Today I am going to show how to create a User Defined Function (UDF) in Microsoft SQL.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[43,6],"tags":[14,15,54],"series":[],"class_list":["post-303","post","type-post","status-publish","format-standard","hentry","category-sql-t-sql","category-sql","tag-sql","tag-t-sql","tag-udf"],"_links":{"self":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/303","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/comments?post=303"}],"version-history":[{"count":10,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/303\/revisions"}],"predecessor-version":[{"id":359,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/303\/revisions\/359"}],"wp:attachment":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/media?parent=303"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/categories?post=303"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/tags?post=303"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/series?post=303"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}