asked on

TSQL - String Changes

Hi Experts,

I have this and it is an Access Query:

iif(Left([companyName],4)="The ",mid([companyName],4) & ", The",[companyName])

Open in new window

What it does is looks for a name that starts with "The" it takes it out from the front and puts it at the end so:

The Book Store
Would be:
Book Store The

I need to use TSQL to do the same thing and need help writing the proper syntax for TSQL which will be an update to the table.

TableName.CompanyName

Please help and thanks....

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amour22015

ASKER

It looks like I will have to do an update.

So I am looking for something like:

Update YourTable
Set Search1 = ????
From YourTable
Where ?????

Can I do an update to the table in question like that?

Thanks for helping....

Amour22015

ASKER

Maybe something like:
Update YourTable
Set Search1 = CASE WHEN LEFT(companyName, 4) = 'The' THEN RIGHT(companyName, LEN(CompanyName) - 4)) + ' The' ELSE CompanyName END as companyName
From YourTable
Where CompanyName Like 'The %'

Thanks for helping

Jim Horn

Same code but UPDATE instead of SELECT...

UPDATE YourTable 
SET CompanyName = CASE WHEN LEFT(companyName, 4) = 'The' THEN RIGHT(companyName, LEN(CompanyName) - 4)) + '  The' ELSE CompanyName END

Open in new window

CREATE TABLE #tmp (companyName varchar(100)) 

INSERT INTO #tmp (companyName) 
VALUES ('The Rumpus Room'), ('Adam 12'), ('Surf Nazis Must Die'), ('Starship Troopers'), ('The Hunger Games')

SELECT 'Before', companyName FROM #tmp

UPDATE #tmp
SET CompanyName = CASE WHEN LEFT(companyName, 4) = 'The' THEN RIGHT(companyName, LEN(CompanyName) - 4) + ' The' ELSE companyName END

SELECT 'After', companyName FROM #tmp

Open in new window

Fore more Access SQL to SQL Server T-SQL converstion tips check out my article at Migrating your Access Queries to SQL Server Transact-SQL

Amour22015

ASKER

Also on the Access Query:

iif(Left([companyName],4)="The ",mid([companyName],4) & ", The",[companyName])

Open in new window

I just notice that it puts a blank in front So like:

The Book Store

Book Store, The

But it comes out as:
Book Store, The

Please help and thanks

Amour22015

ASKER

Great Thanks