String manipulation SQL

Question

I have a row of strings that are in the following format:

'Order was assigned to lastname,firsname'

I need to cut this string down into just the last and first name but it is always a different name for each record.

The 'Order was assigned to' part is always the same.......

Thanks

I am using SQL Server. It is multiple records with different names in each record.

Answer 1

In your specific case you can use something like:

SELECT SUBSTRING(str, 23) FROM table

However, this is not very scalable, should the format of your strings ever change.

If you are using an Oracle database, you would want to use SUBSTR instead.

Edit:

For databases where the third parameter is not optional, you could use SUBSTRING(str, 23, LEN(str))

Somebody would have to test to see if this is better or worse than subtraction, as in Martin Smith's solution but gives you the same result in the end.

Answer 2

For SQL Server

   WITH testData AS
    (
    SELECT 'Order was assigned to lastname,firsname' as Col1 UNION ALL
    SELECT 'Order was assigned to Bloggs, Jo' as Col1
    )

    SELECT SUBSTRING(Col1,23,LEN(Col1)-22) AS Name
    from testData

Returns

Name

---------------------------------------
lastname,firsname
Bloggs, Jo

Answer 3

I would require that a colon or some other delimiter be between the message and the name. Then you could just search for the index of that character and know that anything after it was the data you need...

Example with format changing over time:

CREATE TABLE #Temp (OrderInfo NVARCHAR(MAX))
INSERT INTO #Temp VALUES ('Order was assigned to :Smith,Mary')
INSERT INTO #Temp VALUES ('Order was assigned to :Holmes,Larry')
INSERT INTO #Temp VALUES ('New Format over time :LootAt,Me')


SELECT      SUBSTRING(OrderInfo, CHARINDEX(':',OrderInfo)+1, LEN(OrderInfo))
FROM        #Temp


DROP TABLE #Temp

Answer 4

on MS SQL Server:

declare @str varchar(100) = 'Order was assigned to lastname,firsname'
declare @strLen1 int = DATALENGTH('Order was assigned to ')
declare @strLen2 int = len(@str)
select @strlen1, @strLen2, substring(@str,@strLen1,@strLen2), 
                  RIGHT(@str, @strlen2-@strlen1)

Answer 5

In addition to the SUBSTRING methods, you could also use a REPLACE function. I don't know which would have better performance over millions of rows, although I suspect that it would be the SUBSTRING - especially if you were working with CHAR instead of VARCHAR.

SELECT REPLACE(my_column, 'Order was assigned to ', '')