Trim everything after symbol and also search for other fields SQL

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

Answers


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.


  SELECT 
        PARSENAME(REPLACE('Jim John - 1234567','-','.'),2)

Result:

name      
--------- 
Jim John  

So you can use it as

SELECT 
     PARSENAME(REPLACE(NAME, '-', '.'), 2) NAME
    ,PARSENAME(REPLACE(othername, '-', '.'), 2) othername
    ,streetname
    ,postal
    ,nameID
    ,helper
    ,helperID
FROM [DATABASE].[dbo].[nameDB] WITH (NOLOCK)
WHERE ...

Parsename(),Replace()


Need Your Help

django.db.utils.ProgrammingError: type "int4range" does not exist

django postgresql django-migrations django-database

First time using PostgreSQL specific database fields, more specifically IntegerRangeField. Using Django 1.8.7, PostgreSQL 9.1.13, Psycopg2 2.6.1 and Python 2.7.3 on a Debian virtual machine. Runnin...

non-class rvalues always have cv-unqualified types

c++ c++11 rvalue-reference rvalue

§3.10 section 9 says "non-class rvalues always have cv-unqualified types". That made me wonder...