0

In a particular column, I am trying to extract everything before any whitespace. For example in the string "text1 text2" there are three spaces between "text1" and "text2", and I just want text 1.

But within this same column there could be a string like "text1 text2", and here there are 5 spaces in between text1 and text2.

I tried the following code:

SELECT LEFT(column, CHARINDEX(' ', column) -1 )

But as I quickly learned, this would only work on strings where there is only one space in between the two words.

Any idea on how I can index for whenever any whitespace appears in a string?

sudden_clarity_clarence
  • 303
  • 4
  • 10
  • 1
    Why do you feel that the above would only work when there is only 1 space? `LEFT('text1 text2', CHARINDEX(' ', 'text1 text2') -1 )` returns `'text1'`; why is that *not* correct? – Thom A Jul 12 '22 at 15:55
  • 1
    Is it a [space] or perhaps some other control character(s) https://stackoverflow.com/questions/43148767/sql-server-remove-all-non-printable-ascii-characters/43149139#43149139 – John Cappelletti Jul 12 '22 at 16:33
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Jul 12 '22 at 17:14
  • 1
    Agree with @JohnCappelletti. While _space_ is a _whitespace_ character it doesn't follow that _whitespace_ will always be a _space_ character. Depending on context whitespace often includes other characters like tabs, carriage returns, linefeeds, non-breaking spaces, etc.. If you `select cast('text1 text2' as varbinary(max))` you will see `20` representing space characters, do you see other things like tab `09`, carriage return `0D`, linefeed `0A` in the output? – AlwaysLearning Jul 12 '22 at 21:55
  • `LEFT(column, NULLIF(PATINDEX('[^A-Za-z0-9]', column), 0) -1 )` should do the trick – Charlieface Jul 12 '22 at 22:37

0 Answers0