SQL Server Performance

separating strings/data

Discussion in 'General Developer Questions' started by v1rt, Oct 16, 2009.

  1. v1rt New Member

    We have a column field that contains more than 1000 characters. The vendor asked us to export the data as text file, pipe delimeted. However, they mentioned that if the field is more than 500 characters, it should be separated to another column in the text file.
    Let's just imagine the string below is 1500 characters long and is found in the column from the table.
    abcdefghijklmnopqrstuvwxyz
    They said that they want it separated. That means, each exported text file can have different number of columns.
    It would look like this in text file after it was been exported
    abcdefghij | klmnopqrs | tuvwxyz
    The above is just an example. Each string were cut down to 500 characters but 2 new columns on the text file were added.
    Can we do that using in a SELECT statement? Oh, I export using Tools->Options->Results tab->then Results to file. I just choose the pipe delimiter.
  2. Adriaan New Member

    Assuming we're not talking about a TEXT or NTEXT column, but VARCHAR() or NVARCHAR() something like this might do the trickselect
    substring(c, 1, 3) + case when len(c) > 3 then '|' else '' end
    + substring(c, 4, 3) + case when len(c) > 6 then '|' else '' end
    + substring(c, 7, 3) + case when len(c) > 9 then '|' else '' end
    + substring(c, 10, 3) + case when len(c) > 12 then '|' else '' end
    + substring(c, 13, 3)
    from (select '123456789' c) t

    I included CASE to suppress unnecessary separators ...
    I thought about trying the STUFF() function but it returns NULL if you attempt to add a separator after the last character.
  3. v1rt New Member

    It's in TEXT data type. Do I just change the function that will work for TEXT and use the logic you wrote above using case statement?
  4. v1rt New Member

    I got it to work using your logic. Thank you so much! [:)]
    select len(cast(legal as varchar(8000))),substring(legal,1,1000) +
    case when len(cast(legal as varchar(8000))) > 1000 then '|' else '' end + substring(legal, 1001, 1000) +
    case when len(cast(legal as varchar(8000))) > 2000 then '|' else '' end + substring(legal, 2001, 1000) +
    case when len(cast(legal as varchar(8000))) > 3000 then '|' else '' end + substring(legal, 3001, 1000) +
    case when len(cast(legal as varchar(8000))) > 4000 then '|' else '' end + substring(legal, 4001, 1000) +
    case when len(cast(legal as varchar(8000))) > 5000 then '|' else '' end + substring(legal, 5001, 1000) +
    case when len(cast(legal as varchar(8000))) > 6000 then '|' else '' end + substring(legal, 6001, 1000) +
    case when len(cast(legal as varchar(8000))) > 7000 then '|' else '' end + substring(legal, 7001, 1000),
    * from assign

Share This Page