Like and substring? - 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 2005 Forums

.NET Inside SQL Server (2005)

Like and substring?

Author

Topic

finito
Starting Member

18 Posts

Posted - 2008-12-21 : 00:00:30

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 " (".

I have SQL 2005 as Backend and C# as the Front end.

Thanks in Advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-21 : 00:47:58

I'm not following you, but don't you want this: WHERE PartNo LIKE '%588%'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-21 : 04:03:06

WHERE PartNo LIKE '%588 (%'

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

finito
Starting Member

18 Posts

Posted - 2008-12-21 : 09:30:26

WHERE PartNo LIKE '%588 (%'
will not get:

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

And WHERE PartNo LIKE '%588%'

will get everything.
basically if it were C# I would do this.


String input = "588" // input from User does not have to be 3 chars

int jumper = PartNo.IndexOf(@"("); 

if (jumper > 0)
   String jump = PartNo.Substring(0, PartNo.IndexOf(@"(") - 1); //Remove ( if it exists.
else 
   jump = PartNo

if (jump.Substring(PartNo.length-input.length+1, input.length).equal(input.length)) // Compare input with last few chars of PartNo
then
  Whatever

I hope that makes more sense.

basically in SQL what I think would want it to be (Hypothetical code)

SELECT Substring(PartNo, PartNo.length-input.length+1,input.length) Where PartNo LIKE '588%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-21 : 11:46:44

Did you try the suggestion posted 12/21/2008 : 04:03:06 ?

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

finito
Starting Member

18 Posts

Posted - 2008-12-22 : 01:29:21

Dear Peso please read what I wrote,

WHERE PartNo LIKE '%588 (%'
will get me

1234588 (RS845) | 82.02 | SH1
but not
85221588 | 42.21 | SH2

And I want both for result

I think I got an idea, if it works I shall post it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 02:55:42

Skip the ( in the like string?

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

finito
Starting Member

18 Posts

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

OK I have got it to work like I want it to

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

it was so easy but I guess I was mind blocked, peso you helped me in an unlikely way.

anyway thanks all that helped

SwePeso
Patron Saint of Lost Yaks

30421 Posts

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

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

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