CODEWITHSUNDEEP
sql-serverasp-classicvbscript
msvuze
msvuze

Reputation: 1397

Split within a Split for direct from MS SQL?

I'm using Classic ASP (Yes, I know but I cannot) and MS SQL 2012 as the database and the data type is nvarchar(255) the field name is "RATE_BREAKDOWN"

The data is saved from a textarea and it looks like this:

Line One = 550.00
Accs = 1000.00
Total = 1550.00

Now I would like to loop each line (vbCrLf) and display it in a <TR>

for example like this:

<tr>
<TD> Line One : </TD>
<TD> $ 550.00 </TD>
</tr>

<tr>
<TD> Accs : </TD>
<TD> $ 1000.00 </TD>
</tr>

<tr>
<TD> Total : </TD>
<TD> $ 1550.00 </TD>
</tr>

But how do I do it ?

I tried using a split function to first split the vbCrLf but I have to split within a split (split two times) and I cannot get it to work, plus I think there should be a easier way of doing this.

Here is what I have tried but it's wrong:

    <%
    dim tmpValue,strtxtMSG,x,tmpValueB
    strtxtMSG = objRecordSet("RATE_BREAKDOWN")

    tmpValue = Split(strtxtMSG, vbCrLf)
    tmpValueB = Split(tmpValueB, "=")
    %>

            <% for each x in tmpValueB %>
            <tr>
            <TD> <%=tmpValueB(0)%> : </TD>
            <TD> $ <%=tmpValueB(1)%> </TD>
            </tr>
            <% next %>

As what the problem has to do with ms sql, I wanted to see if ms sql has an option to loop and split the data and then put that into an array, and not display all of it right away. something like this query = "SELECT * FROM TestTable WHERE 1=1 AND SPLIT(SPLIT("RATE_BREAKDOWN","NewLine"),"=")"

The class for the <TD> shows up as text ?

Response.Write "<tr><td>" & join(split(join(split(objLoad("RATE_BREAKDOWN"), vbCrLf), "</td></tr><tr><TD width='285' class='auto-style57'>"), "=")," : </td><td> $ ") & "</td></tr>"

The width='285' class='auto-style57' becomes the text and not part of the page's html/format

Upvotes: 0

Views: 214

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280383

Forget all of the tmp variables and going in and out of <% %> blocks. Just do this:

Response.Write "<tr><td>" & join(split(join(split(strtxtMSG, vbCrLf),
  "</td></tr><tr><td>"), "=")," : </td><td> $ ") & "</td></tr>"

The reason this breaks when you add additional requirements such as class='some-thing' is because now you are splitting by = but you also want some = to stay. So, you can get around this by replacing those = first with something else that won't naturally appear in the data, let's say a pipe | character:

strtxtMSG = Replace(strtxtMSG, "=", "|")

' then split by | instead of =

Response.Write "<tr><td>" & join(split(join(split(strtxtMSG, vbCrLf),
  "</td></tr><tr><td>"), "|")," : </td><td class='some-thing'> $ ") & "</td></tr>"

SQL Server doesn't have any inherent split/join functionality. You could construct a REPLACE() command that does this:

DECLARE @RATE_BREAKDOWN NVARCHAR(255);

SELECT @RATE_BREAKDOWN = N'Line One = 550.00
Accs = 1000.00
Total = 1550.00';

SELECT '<tr><td>' + REPLACE(REPLACE(@RATE_BREAKDOWN, CHAR(13) + CHAR(10), 
  '</td></tr><tr><td>'), ' = ', ' : </td><td> $ ') + '</td></tr>';

And some fancy whiz will probably come along and suggest a FOR XML PATH equivalent, probably something better than this:

DECLARE @RATE_BREAKDOWN NVARCHAR(255);

SELECT @RATE_BREAKDOWN = N'Line One = 550.00
Accs = 1000.00
Total = 1550.00';

DECLARE @r VARCHAR(MAX) = REPLACE(REPLACE(
  (SELECT @RATE_BREAKDOWN FOR XML PATH('td'),ROOT('tr')),
   ' = ', ' : </td><td> $ '), '&#x0D; ', '</td></tr><tr><td>');

SELECT @r;

But both of these are grossly inefficient to perform in T-SQL, and much better handled in the presentation tier. Why should SQL Server know or care about HTML elements like <tr> or <td>? Just because you can do it doesn't mean you should.

Even better, by building a better database schema - if these are separate pieces of data, why are they being munged together in a single column?

Upvotes: 1

Related Questions