Prev: Trimming chars
Next: Finding tables with specific columns
From: nlulla on 16 Jul 2008 12:56
TableName : TableX
Column 1 : name (single line)
Column 2 : Comments (multiple line)

In "Column2" i have comments which are all about roughtly 20 lines but
can vary. What i want to do is for example i want to write a select
query which will display both columns but only show line 13 (or
whichever i want) from column2.

How do i do this in one T-SQL statement (I am on sql 2005)

Many thanks
From: Plamen on 16 Jul 2008 13:55
You can split the comments column to lines and select the line of
interest. Here is one example (the table master..spt_values simulates
auxiliary table with numbers):

CREATE TABLE TableX (
foo_key INT PRIMARY KEY,
foo_name VARCHAR(30),
foo_comment VARCHAR(250));

INSERT INTO TableX
VALUES(1, 'name1', 'line1' + CHAR(13) + CHAR(10) +
'line2' + CHAR(13) + CHAR(10) +
'line3');

SELECT foo_key, foo_name, comment_line_text, comment_line
FROM (
SELECT SUBSTRING(foo_comment, n,
CHARINDEX(CHAR(13) + CHAR(10),
foo_comment + CHAR(13) + CHAR(10), n)
- n) AS
comment_line_text,
n + 1 - LEN(REPLACE(LEFT(foo_comment, n), CHAR(13) +
CHAR(10), '
' )) AS comment_line,
foo_key, foo_name
FROM TableX AS F
CROSS JOIN
(SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 250) AS Nums(n)
WHERE SUBSTRING(CHAR(13) + CHAR(10) + foo_comment, n, 2) = CHAR(13) +
CHAR(10)
AND n < LEN(foo_comment) + 2) AS T
WHERE comment_line = 2;

HTH,

Plamen Ratchev
http://www.SQLStudio.com
From: nlulla on 17 Jul 2008 05:33
Hi Plamen,

thanks for your quick reply, i tried your solution as you posted, and
i gives me following when tried in SQL 2005 DB

1) when i try "WHERE comment_line = 1 " i get one row back with
text " line1"
2) when i try "WHERE comment_line = 2 " i get no rows back
3) when i try "WHERE comment_line = 3 " i get no rows back

I does not give any error messages but somehow the solution only works
for line 1 but not for others.

Regards
nlulla

From: Plamen Ratchev on 17 Jul 2008 09:06
Seems the post broke into multiple lines and copy/paste eliminates one
space. Make sure in the line that calculates 'comment_line' there is a space
in the last REPLACE parameter.

n + 1 - LEN(REPLACE(
LEFT(foo_comment, n),
CHAR(13) + CHAR(10),
' ' )) AS comment_line,

HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: nlulla on 18 Jul 2008 12:33
hi plamen,

it does work now. thanks a lot. but i still have one question, when i
ran the sub select part of your solution, it basically creates 1 row
for every character in the comment, is there a way this can be
narrowed down to just the line rather then for every letter as
otherwise it takes a lot of time if i am going to do this on all rows
in a table.

so basically your example creates below rows in the sub select . Don't
get me wrong, your's is the only solution i have managed to find so
far hence it will surely be useful but if there is any chance we can
reduce the overhead as i will have to run this on good 20K rows.

line1 1 1 name1
ine1 1 1 name1
ne1 1 1 name1
e1 1 1 name1
1 1 1 name1
1 1 name1
line2 3 1 name1
line2 2 1 name1
ine2 2 1 name1
ne2 2 1 name1
e2 2 1 name1
2 2 1 name1
2 1 name1
line3 4 1 name1
line3 3 1 name1
ine3 3 1 name1
ne3 3 1 name1
e3 3 1 name1
3 3 1 name1
4 1 name1

Many thanks
lulla
 |  Next  |  Last
Pages: 1 2
Prev: Trimming chars
Next: Finding tables with specific columns