Replace keyword or a better way?

Question

Post reply

Replace keyword or a better way?

SQLalchemy

SSC-Addicted

Points: 486
More actions
June 18, 2014 at 11:33 am

#287007

I have the following schema from which I am failing to yield (any ideas? - thanks!):
1
100
200
create table names (Place varchar(50));
insert names values ('(1)');
insert names values ('(100)');
insert names values ('(200)');
SELECT
REPLACE(LEFT
(REPLACE(RIGHT(
Place, CHARINDEX(' ', REVERSE(Place))), ')',''))
, '(','')
AS [NUMBER]
from names

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Try this instead

SELECT REPLACE(REPLACE(Place,')',''),'(','')

FROM names

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Eirikur Eiriksson SSC Guru Points: 182884 More actions · Answer 2

Further on Jason's advice

😎

USE tempdb;

GO

create table dbo.names (Place varchar(50));

insert into dbo.names ( Place) values ('(1)');

insert into dbo.names ( Place) values ('(100)');

insert into dbo.names ( Place) values ('(200)');

SELECT

REPLACE(REPLACE(Place,'(',''),')','')

from dbo.names

DROP TABLE dbo.names

SQLalchemy SSC-Addicted Points: 486 More actions · Answer 3

Thanks for the help and the directing me towards a temp table solution... one twist - a new form of data:

create table dbo.names (Place varchar(50));

insert into dbo.names ( Place) values ('Benthill (1)');

insert into dbo.names ( Place) values ('Robbins Nest (100)');

insert into dbo.names ( Place) values ('Peachtree (200)');

with this being the final result needed:

1 Benthill

100 Robbins Nest

200 Peachtree

I can use the REPLACE(REPLACE(Place,'(',''),')','') and get rid of the parentheses but I have no idea how to move the number to the front... thanks for any clues!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Here's what I got from playing a little bit with the information.

SELECT SUBSTRING( Place, CHARINDEX('(', Place ) + 1, CHARINDEX(')', Place ) - CHARINDEX('(', Place ) - 1)

+ ' ' + LEFT(Place, CHARINDEX('(', Place ) - 1)

FROM names

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

SQLalchemy (6/19/2014)
Thanks for the help and the directing me towards a temp table solution... one twist - a new form of data:

Fwiw - the use of tempdb is just for people on forums to be able to create the consumable table and data.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events