Matching characters in a string

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

General SQL Server Forums

New to SQL Server Programming

Matching characters in a string

Author

Topic

Catastrophe
Starting Member

3 Posts

Posted - 2009-12-31 : 06:33:25

Hello, I'm very new to SQL so apologies if I don't exlain this very well...

I have a table containing reference numbers and a sum value of money relating to each reference. Most of my references are unique but there are several instances where the reference number is repeated with one unique character in each one to identify it. Example...

BN1234P001 $100
BN5678P001 $150
BN5678E001 $150

I need a query to match the first six characters in the reference number so that I can check that the sum value of money is the same (or different which is the purpose of my query). I only want to return those references that have a match on the first six characters.

I already have the select, sum, group and order sections in place, which is bringing back all my policy numbers with their sum value, but I'm struggling with there WHERE clause.

SELECT slip, assured_name, SUM (fgu100) 'TIV'
FROM NJ_LIVE_AS_AT
WHERE
GROUP BY slip, assured_name
ORDER BY assured_name

I'd be very grateful if someone could take a look at this for me.

Many thanks

BB

There are no stupid questions - I'm just an inquisitive idiot

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-31 : 06:44:36

Show us an example of the output of your query because your given example data doesn't fit to your shown select.
Which column is holding that values like BN1234P001?

No, you're never too old to Yak'n'Roll if you're too young to die.

Catastrophe
Starting Member

3 Posts

Posted - 2009-12-31 : 06:58:52

slip assured_name TIV
BN0223P1080037 ABC Company 3704236000
BN0136P1090951 DEF Company 562616689
BN2081E1090869 GHI Company 1294330000
BN2081P1090869 GHI Company 1294330000
BN0007P1090147 JKL Company 11585671269
BN0007E1090147 JKL Company 11585671269
BN0007E2090147 JKL Company 255656998

This is my output data. As you can see, ABC and DEF are returned as single rows meaning there is only one policy for that company. GHI and JKL are returned several times showing more than one policy. The first six characters of the slip are unique to the assured, the seventh and eight characters define the seperate policies. I only want to pull back those rows where there is a match in the first six characters so I can check the TIV matches.

Many thanks

BB

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-31 : 07:42:33

quote:
Originally posted by Catastrophe

slip assured_name TIV
BN0223P1080037 ABC Company 3704236000
BN0136P1090951 DEF Company 562616689
BN2081E1090869 GHI Company 1294330000
BN2081P1090869 GHI Company 1294330000
BN0007P1090147 JKL Company 11585671269
BN0007E1090147 JKL Company 11585671269
BN0007E2090147 JKL Company 255656998

This is my output data. As you can see, ABC and DEF are returned as single rows meaning there is only one policy for that company. GHI and JKL are returned several times showing more than one policy. The first six characters of the slip are unique to the assured, the seventh and eight characters define the seperate policies. I only want to pull back those rows where there is a match in the first six characters so I can check the TIV matches.

Many thanks

BB

Post your expected output

Madhivanan

Failing to plan is Planning to fail

Catastrophe
Starting Member

3 Posts

Posted - 2009-12-31 : 07:47:48

This is what I have now
slip assured_name TIV
BN0223P1080037 ABC Company 3704236000
BN0136P1090951 DEF Company 562616689
BN2081E1090869 GHI Company 1294330000
BN2081P1090869 GHI Company 1294330000
BN0007P1090147 JKL Company 11585671269
BN0007E1090147 JKL Company 11585671269
BN0007E2090147 JKL Company 255656998

This is what I want

slip assured_name TIV
BN2081E1090869 GHI Company 1294330000
BN2081P1090869 GHI Company 1294330000
BN0007P1090147 JKL Company 11585671269
BN0007E1090147 JKL Company 11585671269
BN0007E2090147 JKL Company 255656998

As you can see, the first two companies are now missing - what is being returned is where there is more than one instance of the first six characters of the slip reference. My dataset is quite large hence why I'm trying to avoid doing this manually.

Thank you.

BB

There are no stupid questions - I'm just an inquisitive idiot

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-12-31 : 08:12:15

Still makes no sense. Try following the "How to ask" link in my sig, and try again.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-31 : 08:21:09

Try this

select t1.* from your_table as t1 inner join
(
select left(slip,6) as slip from your_table
group by left(slip,6)
having count(*)>1
) as t2 on t1.slip like t2.slip+'%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources