SQLTeam.com | Weblogs | Forums

SUBSTRING(field, 0, 20)


#1

Good day, I am looking for a way to break a text string at the last word inside the limit specified (EG
source text is this "Numerous passing references to the phrase"
I would like to see this in the output "Numerous passing" even though the cutoff is here "Numerous passing ref" How can I do this?


#2
SELECT text_string, 
    LEFT(text_string, 21 - CHARINDEX(' ', REVERSE(LEFT(text_string, 21)))) --<--**
FROM (
    SELECT 'Numerous passing references to the phrase' AS text_string
) AS test_data

#3

Thank you so much!!
I didn't realize that I forgot to add, I'd like to add the rest of that sentence to another cell. How do I do that?


#4

LTRIM is optional, so that the rest of the string doesn't start with a space(s). If you want the remainder to start with a space(s), remove the LTRIM().

SELECT text_string, 
    LEFT(text_string, first_string_length) AS first_string,
    LTRIM(SUBSTRING(text_string, first_string_length + 1, 8000)) AS rest_of_string
FROM (
    SELECT 'Numerous passing references to the phrase' AS text_string
) AS test_data
CROSS APPLY (
    SELECT 21 - CHARINDEX(' ', REVERSE(LEFT(text_string, 21))) AS first_string_length
) AS assign_alias_names1

#5

AWESOME!!! Thank you.