append text at start and end of a specific text

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

append text at start and end of a specific text

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2014-12-08 : 12:38:08

Hi firends ,

I have field call customers where the data is like as follows
John (234),
James,
Jones (123)

I would like to check for the customers value which have the braces and show them this way
john '(234)',
James,
Jones '(123)',

How can i achieve this..Thank You

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-08 : 13:04:02

I assume you mean parentheses, since that's what your sample data shows.

Use REPLACE


select replace(replace(customer, ')', ')</br>'), '(', '<br>(');

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2014-12-08 : 13:31:45

Thank You gbritton..iTw orked but I ahve a second issue..
Some of the values are in this way

Johnston(A1)(123)
How can i use the replace to avoid (A1) like this Johnston(A1)(123)...Thank You

quote:
Originally posted by gbritton

I assume you mean parentheses, since that's what your sample data shows.

Use REPLACE

select replace(replace(customer, ')', ')'), '(', ' (');

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2014-12-08 : 13:32:05

quote:
Originally posted by akpaga

Thank You gbritton..it worked but I have a second issue..
Some of the values are in this way

Johnston(A1)(123)
How can i use the replace to avoid (A1) like this Johnston(A1)(123)...Thank You

quote:
Originally posted by gbritton

I assume you mean parentheses, since that's what your sample data shows.

Use REPLACE

select replace(replace(customer, ')', ')'), '(', ' (');

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2014-12-08 : 13:32:06

quote:
Originally posted by akpaga

Thank You gbritton..it worked but I have a second issue..
Some of the values are in this way

Johnston(A1)(123)
How can i use the replace to avoid (A1) like this Johnston(A1)(123)...Thank You

quote:
Originally posted by gbritton

I assume you mean parentheses, since that's what your sample data shows.

Use REPLACE

select replace(replace(customer, ')', ')'), '(', ' (');

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-08 : 13:55:19

Is that pattern fixed? That is, is it (letternumber) as in (A1)? or is it variable? that will drive the answer

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2014-12-08 : 16:15:36

quote:
Originally posted by gbritton

Is that pattern fixed? That is, is it (letternumber) as in (A1)? or is it variable? that will drive the answer

Its a variable gbritton

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-12-08 : 16:24:51

Will need to adjust the CROSS APPLY if the first (# is not the desired location. But once the CROSS APPLY is adjusted to the correct location, the rest of the code should continue to work fine.


select
    customer,
    case when customer_bold_start = 0 then customer 
         else STUFF(STUFF(customer + ' ', customer_bold_start +
              CHARINDEX(')', substring(customer, customer_bold_start, 20)), 0, '</b>'),
                  customer_bold_start, 0, '<b>')
         end
from (
    select 'John (234)' as customer union all
    select 'James' union all
    select 'Jones (123)' union all
    select 'Smith (a1)(247)'
    ) as test_data
cross apply (
    select PATINDEX('%([0-9]%', customer) AS customer_bold_start
) as ca1

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2014-12-08 : 22:55:31

quote:

Originally posted by ScottPletcher

Will need to adjust the CROSS APPLY if the first (# is not the desired location. But once the CROSS APPLY is adjusted to the correct location, the rest of the code should continue to work fine.


select
    customer,
    case when customer_bold_start = 0 then customer 
         else STUFF(STUFF(customer + ' ', customer_bold_start +
              CHARINDEX(')', substring(customer, customer_bold_start, 20)), 0, '</b>'),
                  customer_bold_start, 0, '<b>')
         end
from (
    select 'John (234)' as customer union all
    select 'James' union all
    select 'Jones (123)' union all
    select 'Smith (a1)(247)'
    ) as test_data
cross apply (
    select PATINDEX('%([0-9]%', customer) AS customer_bold_start
) as ca1

Thank You Scottpletcher

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources