capture those string with "-" - SQL Server Forums

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)

capture those string with "-"

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-06-26 : 20:43:40

Hi,

I have a requirements to capture those words with "-RTR".
May i ask you assistance on how to make this in t-sql. thanks.

Sample:


create table #sample
(item nvarchar(50))
insert into #sample(item) values('HOT3053-RTR/2937-RTR Motorola DRDRZRMX Housing Frame')
insert into #sample(item) values('HOT3053-RTR Motorola DRDRZRMX Housing Frame') 
insert into #sample(item) values('DAM1615-RTR  LP - LCM Assembly') 
insert into #sample(item) values('Scraped Plastic kit HOT2937-RTR  DROIDRAZOR2 Housing Frame')

Result

Item----------------------Item2
-------------------------------------------------------------
HOT3053-RTR/2937-RTR-- Motorola DRDRZRMX Housing Frame
HOT3053-RTR----------- Motorola DRDRZRMX Housing Frame
DAM1615-RTR----------- LP - LCM Assembly
HOT2937-RTR----------- DROIDRAZOR2 Housing Frame

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-26 : 21:58:33

select * from #sample where item like '%-RTR%'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-06-26 : 22:32:19

Hi Tkizer,

thanks for your reply. sorry for not giving a clear requrements.
I wanted to create a separate column for the string with "-RTR" complete name like (HOT3053-RTR)
and the other column will be the string after the -RTR.

Please see the sample result in the main thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-26 : 22:38:49

I couldn't figure out the sample results, was't sure what those dashes were for. Now I'm thinking they are an attempt to space it out for us. Just add code tags around the formatted output so that it's readable.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-26 : 22:42:24

select LEFT(item, charindex('-RTR', item))+'-RTR' as item, substring(item, charindex('-RTR', item)+4, datalength(item)) as item2
from #sample

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-06-26 : 23:22:36

Thank you very much for your prompt reply.
Btw, can i have a condition if in the string does not have -RTR can i place an space or blank else display the value
in this codes.

Select LEFT(item, charindex('-RTR', item))+'-RTR' as item
FROm #sample

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-06-26 : 23:32:24

SOLVED. Thank you very much Tkizer..here is the codes with condition.

CASE WHEN sal.NAME like '%-RTR%' THEN LEFT(sal.NAME, charindex('-RTR', sal.NAME))+'RTR' ELSE '' END as SKU

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-26 : 23:44:48

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-06-27 : 01:31:48

Hi Tkizer,

I just encounter this kind of string, how can i get this Item from the codes you have given,
the result would be HHC1035-RTR and item2 is INCREDIBLE00193. thanks.


CASE WHEN sal.NAME like '%-RTR%' THEN LEFT(sal.NAME, charindex('-RTR', sal.NAME))+'RTR' ELSE '' END as SKU

Item
---------------------------
INCREDIBLE00193 HHC1035-RTR

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-27 : 01:38:50

You'll need to explain that logic.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-27 : 01:38:57

You'll need to explain that logic.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-06-27 : 01:58:56

Running the codes that you have given, it gives me incorrect data specially for the records #5, it shows INCREDIBLE00193 HHC1035-RTR
Instead of HHC1025-RTR. the result should be like this. thanks.


Item1--------------Item2
--------------------------------------------------------
HOT3053--RTR-----Motorola DRDRZRMX Housing Frame
DAM1615--RTR-----LP - LCM Assembly
HHC1035--RTR-----INCREDIBLE00193	

--DDL
create table #sample
(item nvarchar(50))
insert into #sample(item) values('HOT3053-RTR/2937-RTR Motorola DRDRZRMX Housing Frame')
insert into #sample(item) values('HOT3053-RTR Motorola DRDRZRMX Housing Frame') 
insert into #sample(item) values('DAM1615-RTR  LP - LCM Assembly') 
insert into #sample(item) values('Scraped Plastic kit HOT2937-RTR  DROIDRAZOR2 Housing Frame')
insert into #sample(item) values('INCREDIBLE00193 HHC1035-RTR')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources