Trim everything after symbol and also search for other fields SQL

Question

I have a number of fields in a database that i need to pull information from. For the name column the data is in the following format:

Jim John - 1234567

I only want the name section, therefore trim everything from the "-" onwards. I've tried the following

"SELECT REPLACE(LEFT(name, CHARINDEX('-',name)-1),'_',' '), othername, streetname, postal, 
                                        nameID, helper, helperID

        FROM [DATABASE].[dbo].[nameDB] with (nolock) 

        WHERE
              (othername like '" & txtSearch.Text & "' 
               or postal like '" & txtSearch.Text & "') 
               and (postal not like '%Error%' 
               and postal not like '%nas_admin%'
               and postal not like '%NASAdmin%'
               and postal like '[A-z][A-z][A-z]%')"

This doesn't work, is it possible that i can trim the data and also search for other data in other fields?

Thanks for any help

Answer 1

try this using Substring

  SELECT 
    SUBSTRING(name, 0, CHARINDEX('-', name)) AS [name] , othername, streetname, postal, 
                                        nameID, helper, helperID

        FROM [ARDVARC].[dbo].[nameDB] with (nolock) 

        WHERE
              (othername like '" & txtSearch.Text & "' 
               or postal like '" & txtSearch.Text & "') 
               and (postal not like '%Error%' 
               and postal not like '%nas_admin%'
               and postal not like '%NASAdmin%'
               and postal like '[A-z][A-z][A-z]%')"

hope this helps.