Looking for a particular substring in SQL string

Question

I'm trying to find some SQL code and having it doesn't seem to be returning my desired output.

Here is my create/insert statement

CREATE TABLE temp
    ([screenName] varchar(130), [realName] varchar(57))
;

INSERT INTO temp
    ([screenName], [realName])
VALUES
    ('WillyWonka', 'Will Stinson'),
    ('Barbara Smith', 'Barbara Smith'),
    ('JoanOfArc', 'JoanArcadia'),
    ('LisaD', 'Lisa Diddle')
;

What i'm looking for is the rows where the realName column has a space in it ... such as the first two and the fourth line ... and the realName the second word after the space Starts with the letter 'S' and is then followed by any character and then has the letter i, followed by any series of characters. This is where i'm stuck.

  SELECT LEFT(realName,CHARINDEX(' ',realName)-1)
  FROM
  temp
  Where LEFT(realName,CHARINDEX(' ',realName)-1) like 'S%'

although i'm pretty sure what i'm doing there is wrong but can't figure out how to make it correct.

apologies to the changes -- but how would i change the code if the name were to change and possibly have multiple space (Jimmy Dean Stinson) If I wanted to modify it to look from the right?

Thank you.

Answer 1

Use LIKE operator.

SELECT *
FROM   temp
WHERE  realName LIKE '% S_i%'