Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If you create the following function:-</p> <pre><code>create FUNCTION [dbo].WordWrap ( @WrapAt int, @Text nvarchar(1024) ) RETURNS nvarchar(1024) AS BEGIN declare @ReturnVaue nvarchar(1024);--the string to be passed back declare @Snip int;-- the length to snip to the last space before the wrapap value declare @Block int;-- the block number of the piece in the return string set @Block=1;-- initialise the block number set @Text=ltrim(rtrim(@Text));-- clean up the input string while charindex(' ',@Text)&gt;0 begin -- if there are any double spaces set @Text=REPLACE(@Text,' ',' '); -- replace them with single spaces end if (@Text is null or DATALENGTH(@Text)&lt;=@WrapAt) begin -- if the input string is null or short enough for 1 block set @ReturnVaue='&lt;1&gt;'+@Text+'&lt;/1&gt;';-- load it into the return value and we're done end else begin -- otherwise we have some work to do set @ReturnVaue='' -- so let's initialise the return value while DATALENGTH(@Text)&gt;0 begin -- and keep going until we have finished -- if the character after the wrapat is a space or there is a space anywhere before the wrapat if SUBSTRING(@Text,@WrapAt+1,1)=' ' or CHARINDEX(' ',left(@Text,@WrapAt))&gt;0 begin if SUBSTRING(@Text,@WrapAt+1,1)=' ' begin -- if the character after the wrapat is a space set @Snip=@WrapAt-- we can snip to the wrapat end else begin --otherwise we have to snip to the last space before the wrapat set @Snip=@WrapAt-charindex(' ',reverse(left(@text,@WrapAt))); end -- now we can load the return value with snipped text as the current block set @ReturnVaue+='&lt;'+CONVERT(varchar,@Block)+'&gt;'+left(@Text,@Snip)+'&lt;/'+CONVERT(varchar,@Block)+'&gt;'; -- and leave just what's left to process, by jumping past the space (@Snip+2) set @Text=SUBSTRING(@Text,@Snip+2,1024); end else begin-- otherwise we have no space to split to - so we can only cut the string at wrapat -- so we load the return value with the left hand wrapat characters as the current block set @ReturnVaue+='&lt;'+CONVERT(varchar,@Block)+'&gt;'+LEFT(@Text,@WrapAt)+'&lt;/'+CONVERT(varchar,@Block)+'&gt;'; -- and leave just what's left to process, by jumping past the wrapat (@WrapAp+1) set @Text=SUBSTRING(@Text,@WrapAt+1,1024); end set @Block+=1-- increment the block number in case we still have more work to do end end RETURN @ReturnVaue; END go </code></pre> <p>and your table is loaded with the following test data:-</p> <pre><code>create table New_Company_Data ( Company_name varchar(120) ); go insert into New_Company_Data values (null); insert into New_Company_Data values (''); insert into New_Company_Data values (' abc abc abc abc'); insert into New_Company_Data values ('a'); insert into New_Company_Data select REPLICATE('a',40)+REPLICATE('b',40)+REPLICATE('c',40) insert into New_Company_Data values ('Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis vehicula, quam non lobortis molestie, purus dui porta sed.'); go select dbo.WordWrap(40,n.Company_name) from New_Company_Data n </code></pre> <p>you get the following returned:-</p> <pre><code>NULL &lt;1&gt;&lt;/1&gt; &lt;1&gt;abc abc abc abc&lt;/1&gt; &lt;1&gt;a&lt;/1&gt; &lt;1&gt;aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa&lt;/1&gt;&lt;2&gt;bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb&lt;/2&gt;&lt;3&gt;cccccccccccccccccccccccccccccccccccccccc&lt;/3&gt; &lt;1&gt;Lorem ipsum dolor sit amet, consectetur&lt;/1&gt;&lt;2&gt;adipiscing elit. Duis vehicula, quam non&lt;/2&gt;&lt;3&gt;lobortis molestie, purus dui porta sed.&lt;/3&gt; </code></pre> <p>I leave it to you to figure out how to shred the (pseudo xml) string into your target columns which depends how repeatable the solution needs to be. If it's a one off - stick the output from my function into a temporary cargo column in your output table and then fashion update statements to pull each block out, one by one, into the desired output column. If you need to repeat this often, write an SP that creates a temp table and cursors through it and moves the blocks out into the target columns all at the same time. You could, obviously, rip my code apart and use the logic directly to fashion a dynamic update statement (adding a SET &lt;targetfield> to &lt;block value> as each block is snipped out of the input string) to be executed at the end of each enumeration.</p> <p>I leave it to you to decide how to proceed (even if that is to ask a more direct question, giving an idea which way you would like to proceed).</p>
    singulars
    1. This table or related slice is empty.
    1. POSplit a string of length nvarchar(120) without breaking words in SQL Server
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USdav1dsm1th
    1. USdav1dsm1th
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSplit a string of length nvarchar(120) without breaking words in SQL Server
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThanks so much I really appreciate your help
      singulars
      1. PO
      1. USKamran
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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