March 4, 2010 at 9:45 am
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
March 4, 2010 at 10:31 am
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply