Select string till first or second space in string

Question

I have table column that contains in each row data like this:

|                 Simbols              |
|--------------------------------------|
|H412 Text text                        |
|H413 Text text text text              |
|EUH 001 Text text text text text text |
|EUH 006 text text                     |
|EUH 201/201A Text text. Text text     |

And I need from that data get data like this:

|Simbols     |
|------------|
|H412        |
|H413        |
|EUH 001     |
|EUH 006     |
|EUH 201/201A|

I tried with SUBSTRING and CHARINDEX but it till the end don't work... It takes only first space or something like that... QUERY:

SELECT 
CASE 
    WHEN SUBSTRING(Simbols, 1, CHARINDEX(' ', Simbols)) = ''
    THEN Simbols + ' '
    ELSE SUBSTRING(Simbols, 1, CHARINDEX(' ', Simbols))
END 'Simbols'
FROM dbo.table

RESULT:

|  Simbols   |
|------------|
|H412        |
|H413        |
|EUH         |
|EUH         |
|EUH         |

How can I make this work, and where is the problem? Maybe there is different way to get these Simbols?
P.S. "Text text text" is a example, there comes a explanations of "Simbols"

If it is up to the second space then why are the first 2 in your desired results stopping at the first? — gvee, Jan 30 '14 at 12:42
@ASPFox Do you want to remove the character TEXT from each row and display the remaining? — Punter015, Jan 30 '14 at 12:49
Try Regular Expressions as answered in http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql — Geordee Naliyath, Jan 30 '14 at 13:02

gvee · Answer 1 · 2014-01-30T12:59:40.797

5

The CharIndex() function has an optional 3rd parameter - start_location - that will be key here.

SELECT your_column
     , CharIndex(' ', your_column) As first_space
     , CharIndex(' ', your_column, CharIndex(' ', your_column) + 1) As second_space
     , SubString(your_column, 1, CharIndex(' ', your_column, CharIndex(' ', your_column) + 1)) As first_two_words
FROM   your_table

Unfortunately when the CharIndex() function can't find the specified string (in this case a single space ' ') it will return 0 (zero).

This means that if there isn't a first or second space the result of first_two_words in my above example will return an empty string as SubString(your_column, 1, 0) = ''.

To get around this you need to be a little clever.

Essentially, if second_space = 0 then we need to return the full string. We have a few options for this:

SELECT your_column
     , CharIndex(' ', your_column) As first_space
     , CharIndex(' ', your_column, CharIndex(' ', your_column) + 1) As second_space
     , SubString(your_column, 1, CharIndex(' ', your_column, CharIndex(' ', your_column) + 1)) As first_two_words
     , SubString(your_column, 1, Coalesce(NullIf(CharIndex(' ', your_column, CharIndex(' ', your_column) + 1), 0), Len(your_column))) As first_two_words_option1
     , CASE WHEN CharIndex(' ', your_column, CharIndex(' ', your_column) + 1) = 0 THEN your_column ELSE SubString(your_column, 1, CharIndex(' ', your_column, CharIndex(' ', your_column) + 1)) END As first_two_words_option2
FROM   (
        SELECT 'one' As your_column
        UNION ALL SELECT 'one two'
        UNION ALL SELECT 'one two three'
        UNION ALL SELECT 'one two three four'
       ) As x

edited Jan 30 '14 at 12:59

answered Jan 30 '14 at 12:50

gvee

16,732
35
50

Very close "first_two_words_option1" and "first_two_words_option2" but still takes first text from "H412" Symbol column :( – ASPFox Jan 30 '14 at 13:04
@ASPFox correct. This query answers your question title "Select string till second space in string". You did not respond to my comment on your question so I will ask you here again: "If it is up to the second space then why are the first 2 in your desired results stopping at the first?". Without you telling us **why** these cases are different we can't provide answers. – gvee Jan 30 '14 at 13:30
Sorry for that! question title is little confusing and sorry about that too. But in description I gave a example of results what I need an where is the problem! There is many type of Simbols and explanations of these Simbols in DB in one row like "H412 Text text" or "EUH 201/201A Text text. Text text" this is the Simbol "H412" or "EUH 201/201A" and Simbols explanations is "Text text", did I answered on your question? :) – ASPFox Jan 30 '14 at 13:41
@ASPFox No, I'm afraid that doesn't answer the question. What **rule** is required to determine whether the first space or second space is the one you want to use? – gvee Jan 30 '14 at 15:14
@ASPFox I've had a guess at your real requirements. Does the following query work for you? http://pastebin.com/ArDjgd4P – gvee Jan 31 '14 at 14:08
OK! Forget about spaces, forget it! I need only "H412", "EUH 201/201A" etc Simbols. Not that "Text" It is possible or not? Thanks, and sorry about that misunderstanding question title, I admit it's incorrect, and I don't know what else to call this question, sorry! :/ – ASPFox Feb 03 '14 at 09:48
@ASPFox have you tried the code I posted a link to in my last comment? – gvee Feb 04 '14 at 09:56

Ajay · Answer 2 · 2014-01-30T13:25:11.270

1

Try this: It works

SELECT CASE WHEN charindex(' ', Simbols, charindex(' ', Simbols) + 1) = 0
THEN Simbols
ELSE LEFT(Simbols, charindex(' ', Simbols, charindex(' ', Simbols) + 1))
END
FROM dbo.table

Here is screenshot what I tried. enter image description here

Here is new EDIT

SELECT REPLACE(Simbols, 'text', '') FROM dbo.table

Here is screen shot

enter image description here

edited Jan 30 '14 at 13:25

answered Jan 30 '14 at 13:02

Ajay

6,418
18
79
130

Very close but still takes first text from "H412" Simbols column :( – ASPFox Jan 30 '14 at 13:06
Yes but look at 1 and 2 Simbol There is "Text", the point is that I don't need that "Text" I need only simbols "H412" "H413" "EUH 001" "EUH 006"... not "H412 Text" "H413 Text". Idea is not bad, very close, but there is something missing... :( – ASPFox Jan 30 '14 at 13:20
That "Text" is only a example, there is different text! Sorry for not mentioning that. – ASPFox Jan 30 '14 at 13:29

Suresh Kamrushi · Answer 3 · 2014-01-30T13:02:41.907

0

Try something like this:

select TRIM(REPLACE(lower(type),"text" ,"")) as T, type from supportContacts

Sql Fiddle: http://sqlfiddle.com/#!2/d5cf8/4

for more info :http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

edited Jan 30 '14 at 13:02

answered Jan 30 '14 at 12:53

Suresh Kamrushi

15,627
13
75
90

Select string till first or second space in string

3 Answers3