Query issue

• SQLAJ

  Ten Centuries

  Points: 1359

  More actions

  March 4, 2010 at 9:45 am

  #386436

  I am trying to filter out data from a column with FirstName. The problem I have is some of the fields have middle initials included.

  I have some of the filter working. But having trouble completing the query. What I am doing is using CHARINDEX to find the "space" between the first name and the middle initial. Using this I wanted to grab just the strings that have a return index of greater than 0.

  If they have a zero they don't have a middle initial. I was then trying to use a CASE statement to just grab the ones GREATER THAN zero. The CASE STATEMENT does not like the GREATER THAN symbol (>) or GT.

  Anyone have a thought, using what I already have?

  SQL Server Database Error: Incorrect syntax near '>'.

  SQL Server Database Error: Incorrect syntax near 'GT'

  SELECT

  TFirstName AS FirstName

  , LTRIM(SUBSTRING(TFirstName, CHARINDEX(' ', TFirstName) ,2)) AS MiddleName

  , CHARINDEX(' ', TFirstName)

  , CASE CHARINDEX(' ', TFirstName)

  WHEN CHARINDEX(' ', TFirstName) GT 0 THEN SUBSTRING(TFirstName, CHARINDEX(' ', TFirstName) ,2) AS MiddleName

  END

  FROM dbo._stagingTVP

  ______________________________
  AJ Mendo | @SQLAJ

• SQLAJ

  Ten Centuries

  Points: 1359

  More actions

  March 4, 2010 at 10:31 am

  #1128620

  Got it figured out. Just me being stupid. Here is the solution.

  SELECT

  TFirstName AS FirstName

  , LTRIM(SUBSTRING(TFirstName, CHARINDEX(' ', TFirstName) ,2)) AS MiddleName

  , CHARINDEX(' ', TFirstName)

  , CASE

  WHEN CHARINDEX(' ', TFirstName) GT 0 THEN SUBSTRING(TFirstName, CHARINDEX(' ', TFirstName) ,2) AS MiddleName

  END

  FROM dbo._stagingTVP

  ______________________________
  AJ Mendo | @SQLAJ