CODEWITHSUNDEEP
sqlsql-serverstringt-sqlsql-server-2008-r2
sqluser
sqluser

Reputation: 5672

How to get the first n characters of a string but ending with a whole word

I have a string and want to get the first n characters of this string but ends with a whole word.

DECLARE @text VARCHAR(MAX) = '123 4056 78789 abcdefg 2222222222 3333 444 55555'
        ,@length INT = 23

I have written this query so far:

REVERSE(STUFF(REVERSE(LEFT(@text, @length)), 1, CHARINDEX(' ',REVERSE(LEFT(@text, @length))),''))

The output for @length = 23 is 123 4056 78789 abcdefg

But for @length = 22 is 123 4056 78789 which is not correct and should be still 123 4056 78789 abcdefg

Thanks!

EDIT

Just to clarify my question:

From @length = 22 till @length = 32 I should get 123 4056 78789 abcdefg

Upvotes: 1

Views: 1168

Answers (5)

David Faber
David Faber

Reputation: 12485

If I understand your requirements correctly, I think you want the following:

WITH x AS (
    SELECT '123 4056 78789 abcdefg 2222222222 3333 444 55555' AS [text], 22 AS [length]
)
SELECT LEFT([text], LEN([text]) - CHARINDEX(' ', REVERSE([text]), LEN([text]) - [length]))
  FROM x;

(I put everything into the WITH clause just so I can refer to the column names instead of plugging in your values.)

Please see SQL Fiddle demo here. I tested it with values of 22 and 32 for length.

UPDATE per comments below:

WITH x AS (
    SELECT '123 4056 78789 abcdefg 2222222222 3333 444 55555' AS [text], 22 AS [length]
)
SELECT LEFT([text], LEN([text]) - CHARINDEX(' ', REVERSE([text]) + ' ', LEN([text]) - [length]) + 1)
  FROM x;

Upvotes: 3

Recursive
Recursive

Reputation: 952

Updated with length.

DECLARE @str VARCHAR(200)='123 4056 78789 abcdefg 2222222222 3333 444 55555',
        @len INT=22
SET @str=Reverse(Substring(@str, 1, @len))
SELECT Reverse(Substring(@str, Patindex('%[a-z]%', @str), @len))

Without length

DECLARE @str VARCHAR(200)='123 4056 78789 abcdefg 2222222222 3333 444 55555'
set @str=REVERSE(@str)
SELECT reverse(Substring(@str, 
             Patindex('%[a-z]%',@str), 
             Charindex(' ', @str, Patindex('%[a-z]%',@str) )-Patindex('%[a-z]%',@str)))

Upvotes: 0

Rooke
Rooke

Reputation: 11

DECLARE @text VARCHAR(MAX) = '123 4056 78789 abcdefg 2222222222 3333 444 55555'
        ,@length INT = 23

SELECT SUBSTRING(REPLACE(RTRIM(LTRIM(@text)),' ',''),1,@length) AS [Text Output]

I included the RTRIM() and LTRIM() to remove any white spaces before and after the string. Then Replace all spaces in the string. Finally get the sub-string of the length (whatever @length is set to).

Upvotes: -1

Will Wainwright
Will Wainwright

Reputation: 332

I think I understand your requirements.

What you need to do is to find the first space after the length, then return all the characters up until that point.

Try this:

SELECT LEFT(@text + ' ',CHARINDEX(' ',@text,@length))

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300539

select left(@text, case when charindex(' ', @text, @length) = 0 
                        then LEN(@text) 
                        else charindex(' ', @text, @length) - 1 
                   end)

Upvotes: 1

Related Questions