September 16, 2016 at 6:05 am
I need to select text up to the first carriage return and or line feed. This is used as the subject i our system. The rest is in the body of the text.
Not all entries have CR LF, but I need to get that text anyway.
I don't know how to do this.
The table is crm7.text
The column with the text entries is text (crm7.text.text)
It could look something like this:
select
text_id
,text AS body
,Subject = ?????
from crm7.text
Thank you if anyone can help
September 16, 2016 at 7:20 am
Solution inspired by Paul White's solution at: http://www.sqlservercentral.com/Forums/Topic895462-392-1.aspx
The 999999 value used in the ISNULL expression is arbitrary and just designed to get the whole string when a CR or LF isn't found. It could also use a LEN(txtField) depending on your preference.
CREATE TABLE #test
(
txtField varchar(100)
)
INSERT INTO #test SELECT 'One Two Three Four'
INSERT INTO #test SELECT 'One Two' + CHAR(10) + 'Three Four'
INSERT INTO #test SELECT 'One Two' + CHAR(13) + 'Three Four'
INSERT INTO #test SELECT 'One Two' + CHAR(10) + CHAR(13) + 'Three Four'
SELECT
txtField
,SUBSTRING
(
t.txtField
,0
,ISNULL((
-- Find the lowest of the positions found
SELECT
MIN(Positions.pos)
FROM
(
-- Try to find both patterns
SELECT CHARINDEX(CHAR(10), T.txtField) pos
UNION ALL
SELECT CHARINDEX(CHAR(13), T.txtField) pos
) Positions
WHERE
Positions.pos > 0-- Exclude cases where no match ws found
), 999999)
)subjectTxt
FROM
#test T
Hope this helps
September 16, 2016 at 7:35 am
DECLARE @someValues TABLE (
[text] nvarchar(max)
);
INSERT INTO @someValues
SELECT N'This is a
multiline string' AS s
UNION ALL
SELECT N'But this is a'+ CHAR(10) +'unix multiline string' AS s;
SELECT subject
FROM @someValues AS sv
CROSS APPLY (
SELECT SUBSTRING([text],1,
CASE
WHEN NULLIF(CHARINDEX(NCHAR(13), sv.[text], 1),0) > CHARINDEX(NCHAR(10), sv.[text], 1) THEN CHARINDEX(CHAR(10), sv.[text], 1)
ELSE CHARINDEX(NCHAR(10), sv.[text], 1)
END
) AS subject
) AS h
-- Gianluca Sartori
September 16, 2016 at 7:49 am
Thank you, this was the answer I needed. Since this will be part of a larger script in excel this had the simplicity needed.
Thank you again!:-)
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply