How to get sub text with compare.

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 2005 Forums

Transact-SQL (2005)

How to get sub text with compare.

Author

Topic

finito
Starting Member

18 Posts

Posted - 2008-12-20 : 03:56:01

Ok I will try my best to make sense.

I have Table of Part Numbers and Supersede this is how my table looks like:

PartNo | Cost | Location
-----------------------------------------
1234588 (RS845) | 82.02 | SH1
RS845 (1234588) | 12.02 | SH1
85221588 | 42.21 | SH2
XYZ123 | 50.85 | SH1
1235447 (SER12) | 12.85 | SH2
SER12 (1235447) | 74.52 | SH1

What I want is to search part number with last or first few number or letters of the part.

What I did is:
SELECT PartNo, Cost, Location FROM Products WHERE LIKE '588%'

What I get is:

PartNo | Cost | Location
-----------------------------------------
85221588 | 42.21 | SH2

What I want to get is:

PartNo | Cost | Location
-----------------------------------------
1234588 (RS845) | 82.02 | SH1
85221588 | 42.21 | SH2

I need a way to concatenate (transact) the PartNo before the " (".

Thanks in Advance.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-22 : 00:51:08

declare @str int
select @str=charindex('(',partno,1) from costloc
select location,cost,substring(partno,1,case when @str = 0 then len(partno) else @str+1 end )as part into #temp from costloc
select c.partno,t.cost,t.location from costloc c
inner join (select location,cost,part from #temp where part like '%588') t on t.cost= c.cost and c.location = t.location
where substring(partno,1,case when @str = 0 then len(partno) else @str+1 end ) like t.part

drop table #temp

finito
Starting Member

18 Posts

Posted - 2008-12-22 : 01:35:51

Wow, SQL PRON

I didn't know you could do that, can you do that in MSSQL.

I had a simpler idea a while ago

SELECT PartNo, Cost, Location FROM Products WHERE PartNo LIKE '588%' OR WHERE PartNo LIKE '%588 (%'

but thanks now I know you can do crazy stuff like that. I will try it once I get back to my work PC.

Thanks again.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-22 : 01:51:49

quote:
Originally posted by finito

Wow, SQL PRON

I didn't know you could do that, can you do that in MSSQL.

I had a simpler idea a while ago

SELECT PartNo, Cost, Location FROM Products WHERE PartNo LIKE '588%' OR WHERE PartNo LIKE '%588 (%'

but thanks now I know you can do crazy stuff like that. I will try it once I get back to my work PC.

Thanks again.

Ur welcome

select * from costloc WHERE partno LIKE '%588%(%' or partno LIKE '%588%'

if use like this u will get o/p as
1234588 (RS845) 82.02 SH1
RS845 (1234588) 12.02 SH1
85221588 42.21 SH2

ur requrirement is not to get the highlighted out in ur result

finito
Starting Member

18 Posts

Posted - 2008-12-22 : 02:16:33

Not really since 588) is not the same as 588 (

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-22 : 04:43:26

Try this
SELECT * FROM table WHERE partno LIKE '%588%(%' OR partno LIKE '%588' AND partno NOT LIKE '%588%)%'

Jai Krishna

finito
Starting Member

18 Posts

Posted - 2008-12-22 : 04:48:30

Jai Krishna why do AND partno NOT LIKE '%588%)%'?

LIKE '%588%'

will give 588 anywhere in there number you don't understand the question,

I want to search the database without supercede if it exists. and find a number that matches the last few number with the quesry

I did

SELECT PartNo, Cost, Location FROM Products WHERE PartNo LIKE '%588' OR '%588 (%'

it works perfect no problems whatsoever.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-22 : 04:54:31

quote:
Originally posted by finito

Jai Krishna why do AND partno NOT LIKE '%588%)%'?

LIKE '%588%'

will give 588 anywhere in there number you don't understand the question,

I want to search the database without supercede if it exists. and find a number that matches the last few number with the quesry

I did

SELECT PartNo, Cost, Location FROM Products WHERE PartNo LIKE '%588' OR '%588 (%'it works perfect no problems whatsoever.

just small correction finito

SELECT PartNo, Cost, Location FROM costloc WHERE PartNo LIKE '%588' OR PartNo LIKE '%588 (%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 04:56:13

Same question here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=116624

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources