5 Replies - 14557 Views - Last Post: 09 January 2013 - 05:54 AM Rate Topic: -----

#1 livium  Icon User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 519
  • Joined: 21-December 08

how can I get a substring in SQL SERVER using space as separator

Posted 14 July 2011 - 12:27 AM

Hi!

I am wondering how can I get the first substring in SQL SERVER from a string using space as separator.

for example:
if I have this "word1 word2 word3", i want to get the first word which is "word1"
of course, i do not know the length of the first word, so i cannot use the substr function

Any ideas?

P.S.: i want to do this is a select query

Thanks!

This post has been edited by livium: 14 July 2011 - 12:32 AM


Is This A Good Question/Topic? 1
  • +

Replies To: how can I get a substring in SQL SERVER using space as separator

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: how can I get a substring in SQL SERVER using space as separator

Posted 14 July 2011 - 12:42 AM

SQL has CHARINDEX and SUBSTRING, so this should work:
SELECT
    CASE CHARINDEX(' ', MyTable.MyValue, 1)
    WHEN 0 THEN MyTable.MyValue
    ELSE SUBSTRING(MyTable.MyValue, 1, CHARINDEX(' ', MyTable.MyValue, 1) - 1)
    END
AS FirstWord
FROM MyTable


I haven't got SQL Server on this machine though, so I can't test it.
Was This Post Helpful? 2
  • +
  • -

#3 livium  Icon User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 519
  • Joined: 21-December 08

Re: how can I get a substring in SQL SERVER using space as separator

Posted 14 July 2011 - 12:45 AM

It works!
Man, I thank you so much.
Have a nice day!

This post has been edited by livium: 14 July 2011 - 12:51 AM

Was This Post Helpful? 0
  • +
  • -

#4 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: how can I get a substring in SQL SERVER using space as separator

Posted 14 July 2011 - 12:56 AM

Gee a +1 would have been nice :rolleyes:
Was This Post Helpful? 1
  • +
  • -

#5 livium  Icon User is offline

  • D.I.C Addict

Reputation: 0
  • View blog
  • Posts: 519
  • Joined: 21-December 08

Re: how can I get a substring in SQL SERVER using space as separator

Posted 14 July 2011 - 01:06 AM

Sorry, I forgot! :)
Was This Post Helpful? 0
  • +
  • -

#6 cgalhano  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 09-January 13

Re: how can I get a substring in SQL SERVER using space as separator

Posted 09 January 2013 - 05:54 AM

Very good #4 e_i_pi ;) nice job
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1