CODEWITHSUNDEEP
sqlsql-servert-sql
user2799983
user2799983

Reputation: 71

Separating a string in sql

We have a field in our table as LastName,FirstName(FromSomewhere) and I need to separate the string into lastNAme firstname and ignore the (FromSomewhere) with a select statement. Any help is really appreciated.

I tried with

Select substring(
         LEFT(_Owner, charindex('(', _Owner) - 1), 
             charindex(',', _Owner) + 1, len(LEFT(_Owner, cha‌​rindex('(', _Owner) - 1))) 
from CC;

is what i have tried and i get an error as

Msg 536, Level 16, State 5, Line 1 Invalid length parameter passed to the SUBSTRING function.

Upvotes: 0

Views: 163

Answers (3)

Kaf
Kaf

Reputation: 33809

Assuming you are on sql-server 2005 or above:

Fiddle demo

;with cte as (
   select _Owner, charindex(',',_Owner) first, charindex('(',_Owner) second 
   from CC
)
select left(_Owner,first-1) lastName,
       substring(_Owner, first+1, second - (first+1)) firstName
from cte

Upvotes: 2

paul
paul

Reputation: 22001

So long as you're only doing this once (to split the data into separate columns for FirstName, LastName) then you could do it like so. Performance would be horrible if you wanted to use the following technique in a regular query though.

declare @myString nvarchar(200) = 'LastName,FirstName(FromSomewhere)'

select  SUBSTRING(@myString, 1, CHARINDEX(',', @myString, 1) - 1),
        SUBSTRING(@myString, CHARINDEX(',', @myString, 1) + 1, CHARINDEX('(', @myString, 1) - CHARINDEX(',', @myString, 1) - 1)

Upvotes: 0

ngneema
ngneema

Reputation: 444

Read on Substring

length

Is a positive integer or bigint expression that specifies how many characters of the expression will be returned. If length is negative, an error is generated and the statement is terminated. If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.

Upvotes: 0

Related Questions