CODEWITHSUNDEEP
sqlsql-server
user380432
user380432

Reputation: 4769

String manipulation SQL

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.

Upvotes: 1

Views: 3523

Answers (5)

MikeD
MikeD

Reputation: 3368

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.

Upvotes: 3

DiningPhilanderer
DiningPhilanderer

Reputation: 2767

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

Upvotes: 0

Tom H
Tom H

Reputation: 47444

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 ', '')

Upvotes: 1

TheVillageIdiot
TheVillageIdiot

Reputation: 40497

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)

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452977

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

Upvotes: 1

Related Questions