Case Statement(Help Please)

I am trying to use a case statement to pull bad phone numbers out of my
company table into a readable format. I am using SQL Server 2000 SP4.
The phone numbers, in my inherited database, are in any number of formats,
not complete, etc, and I need to pull a report showing only the good phone
numbers.
My Case statement returns the error below the statement.
-- SELECT SUBSTRING((RTRIM(phone) + ' '+
RTRIM(phone) + ' '), 1, 25) AS Name, phone,
Phone =
CASE
WHEN SUBSTRING(phone, 1, 2) > len(10) THEN 'Phone'
WHEN SUBSTRING(phone, 1, 2) < len(10) THEN 'Phone'
END
FROM company
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '+(' to a column of data type int.
RayRay wrote on Fri, 11 Nov 2005 07:05:07 -0800:

> I am trying to use a case statement to pull bad phone numbers out of my
> company table into a readable format. I am using SQL Server 2000 SP4.
> The phone numbers, in my inherited database, are in any number of formats,
> not complete, etc, and I need to pull a report showing only the good phone
> numbers.
> My Case statement returns the error below the statement.
> -- SELECT SUBSTRING((RTRIM(phone) + ' '+
> RTRIM(phone) + ' '), 1, 25) AS Name, phone,
> Phone =
> CASE
> WHEN SUBSTRING(phone, 1, 2) > len(10) THEN 'Phone'
> WHEN SUBSTRING(phone, 1, 2) < len(10) THEN 'Phone'
> END
> FROM company
> Server: Msg 245, Level 16, State 1, Line 1
> Syntax error converting the varchar value '+(' to a column of data type
> int.
SUBSTRING(phone, 1, 2) > len(10) is the problem
This takes the first 2 characters of the phone column, and compares it to
the length of the string '10' (implicit conversion of the LEN expression to
a string). The first 2 characters are '(+', and due to implicit conversion
will try to convert that to an integer to compare to the value 2.. What
exactly are you trying to achieve here? It makes no sense.
Dan|||I am trying to pull good phone numbers from a list of bad numbers.
For Example:+ () -
+ () -3681
+ () -8544
+ () ?
+ () ?
+ () 0-+00-00
+ () 0-00
+ () 0-00
+ () 0-00--00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-000
+ () 00-000
+ () 011-1-4756-6900
+ () 011-234-1266-89
+ () 011-2711-320-50
+ () 011-2711-377-38
+ () 011-322-545-252
+ () 011-331-4323-20
+ () 011-358-013360
+ () 011-418-643-307
+ () 011-44-002-7551
+ () 011-45-4468-446
+ () 011-468-719-500
+ () 011-495-254-991
+ () 011-603-707-449
+ () 011-612-4655-24
I inhereted this database from someone who just put stuff in anyway they
wanted. Now I need to pull out the ones formatted corrected so they can be
inputted into a new system.
Ray
"Daniel Crichton" wrote:

> Ray wrote on Fri, 11 Nov 2005 07:05:07 -0800:
>
> SUBSTRING(phone, 1, 2) > len(10) is the problem
> This takes the first 2 characters of the phone column, and compares it to
> the length of the string '10' (implicit conversion of the LEN expression t
o
> a string). The first 2 characters are '(+', and due to implicit conversion
> will try to convert that to an integer to compare to the value 2.. What
> exactly are you trying to achieve here? It makes no sense.
> Dan
>
>|||Ray wrote on Fri, 11 Nov 2005 07:39:16 -0800:

> I am trying to pull good phone numbers from a list of bad numbers.
Sorry, I guess I wasn't being clear enough. From the SELECT statement you
provided I can't figure out what you're trying to do. I can only guess that
you're looking for strings of at least 10 characters after the +( at the
start, but as to what you want returned it makes no sense - why would you
display the first 25 characters of the phone number concatentated to itself
as the name column, and then show the phone number again as phone, and then
appear to just put the string 'Phone' into another column called Phone? A
long list of numbers doesn't explain anything. What is a well formatted
phone number? None of those I would consider well formatted, but then again
I'm a UK resident and we have a different number format here.
Dan|||Daniel Crichton wrote:
> Ray wrote on Fri, 11 Nov 2005 07:39:16 -0800:
>
> Sorry, I guess I wasn't being clear enough. From the SELECT statement
> you provided I can't figure out what you're trying to do. I can only
> guess that you're looking for strings of at least 10 characters after
> the +( at the start, but as to what you want returned it makes no
> sense - why would you display the first 25 characters of the phone
> number concatentated to itself as the name column, and then show the
> phone number again as phone, and then appear to just put the string
> 'Phone' into another column called Phone? A long list of numbers
> doesn't explain anything. What is a well formatted phone number? None
> of those I would consider well formatted, but then again I'm a UK
> resident and we have a different number format here.
Another idea thrown into the mix: write a user defined function that
either maps a given phone number to a valid one or maps phone numbers to
something like "ok", "maybe", "crap". Then you can easily select
your_function(phone) = 'ok'.
Kind regards
robert

Case Sensitive Columns

Setup: SQL Server 2000 Standard
I have a column that contains passwords that are made up of a mix of
uppercase and lowercase letters and numbers. They're supposed to be case
sensitive, but aren't. How can I change the column settings so that they are
case sensitive?
Thanks in advance.
John Steen
It appears your database default collation is case-insensitive. You can
change the collation for a specific column to a case-sensitive one with
ALTER TABLE:
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
INSERT INTO MyTable VALUES('password')
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
ALTER TABLE MyTable
ALTER COLUMN MyPassword varchar(10)
COLLATE SQL_Latin1_General_CP1_CS_AS
--row not found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'password'
Hope this helps.
Dan Guzman
SQL Server MVP
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:CE9A1A9F-36C1-4E9E-90AE-BD1092912E7E@.microsoft.com...
> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they
> are
> case sensitive?
> Thanks in advance.
> John Steen
>
|||You can also change the collation on the fly with the collate clause in
any SQL experssion in a stattement :
SELECT ...
WHERE MyColumn = MyData COLLATE FrenchBIN
A +
Dan Guzman a crit :
> It appears your database default collation is case-insensitive. You can
> change the collation for a specific column to a case-sensitive one with
> ALTER TABLE:
> CREATE TABLE MyTable
> (
> MyPassword varchar(10)
> )
> INSERT INTO MyTable VALUES('password')
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> ALTER TABLE MyTable
> ALTER COLUMN MyPassword varchar(10)
> COLLATE SQL_Latin1_General_CP1_CS_AS
> --row not found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'password'
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
That's another method that to achieve the desired result but a consideration
is that the expression won't be sargable due to the different collations.
However, one can include criteria for both collations in the WHERE clause so
that indexes can be efficiently used. The example below shows how one can
use the technique. This might not be an issue in the OP's case since it is
likely that the actual WHERE clause also includes other indexed criteria
like UserID.
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
GO
CREATE INDEX MyTable_Index1 ON MyTable(MyPassword)
GO
INSERT INTO MyTable VALUES('password')
GO
SET SHOWPLAN_ALL ON
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
GO
--index scan
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' COLLATE French_BIN
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' AND
MyPassword = 'PASSWORD' COLLATE French_BIN
GO
SET SHOWPLAN_ALL OFF
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ersWOBtMGHA.1192@.TK2MSFTNGP11.phx.gbl...
> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
>
> SELECT ...
> WHERE MyColumn = MyData COLLATE FrenchBIN
> A +
> Dan Guzman a crit :
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
|||Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle my
way through it that way if necessary, but is there a way to do this in
Enterprise Manager? Can I do this through the "Design Table" atributes?
Thanks,
John Steen
"John Steen" wrote:

> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they are
> case sensitive?
> Thanks in advance.
> John Steen
>
|||On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
<moderndads(nospam)@.hotmail.com> wrote:

>Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle my
>way through it that way if necessary, but is there a way to do this in
>Enterprise Manager? Can I do this through the "Design Table" atributes?
Hi John,
Yes, you can. Click the column, go to the lower pane and change the
entry in the field "Colaltion". You can click the "..." button to the
right of this field to get an assisted dialog.
But you should learn how to write SQL for this - it gives you so much
more control! (And Enterprise Manager uses some REALLY inefficient ways
to make some changes...)
Hugo Kornelis, SQL Server MVP
|||Thanks, Hugo. And you're right, I do need to learn to write more than simple
SQL scripts. And as soon as I find the time... :-/
John Steen
"Hugo Kornelis" wrote:

> On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
> <moderndads(nospam)@.hotmail.com> wrote:
>
> Hi John,
> Yes, you can. Click the column, go to the lower pane and change the
> entry in the field "Colaltion". You can click the "..." button to the
> right of this field to get an assisted dialog.
> But you should learn how to write SQL for this - it gives you so much
> more control! (And Enterprise Manager uses some REALLY inefficient ways
> to make some changes...)
> --
> Hugo Kornelis, SQL Server MVP
>

Case Sensitive Columns

Setup: SQL Server 2000 Standard
I have a column that contains passwords that are made up of a mix of
uppercase and lowercase letters and numbers. They're supposed to be case
sensitive, but aren't. How can I change the column settings so that they ar
e
case sensitive?
Thanks in advance.
John SteenIt appears your database default collation is case-insensitive. You can
change the collation for a specific column to a case-sensitive one with
ALTER TABLE:
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
INSERT INTO MyTable VALUES('password')
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
ALTER TABLE MyTable
ALTER COLUMN MyPassword varchar(10)
COLLATE SQL_Latin1_General_CP1_CS_AS
--row not found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'password'
Hope this helps.
Dan Guzman
SQL Server MVP
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:CE9A1A9F-36C1-4E9E-90AE-BD1092912E7E@.microsoft.com...
> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they
> are
> case sensitive?
> Thanks in advance.
> John Steen
>|||You can also change the collation on the fly with the collate clause in
any SQL experssion in a stattement :
SELECT ...
WHERE MyColumn = MyData COLLATE FrenchBIN
A +
Dan Guzman a crit :
> It appears your database default collation is case-insensitive. You can
> change the collation for a specific column to a case-sensitive one with
> ALTER TABLE:
> CREATE TABLE MyTable
> (
> MyPassword varchar(10)
> )
> INSERT INTO MyTable VALUES('password')
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> ALTER TABLE MyTable
> ALTER COLUMN MyPassword varchar(10)
> COLLATE SQL_Latin1_General_CP1_CS_AS
> --row not found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'password'
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
That's another method that to achieve the desired result but a consideration
is that the expression won't be sargable due to the different collations.
However, one can include criteria for both collations in the WHERE clause so
that indexes can be efficiently used. The example below shows how one can
use the technique. This might not be an issue in the OP's case since it is
likely that the actual WHERE clause also includes other indexed criteria
like UserID.
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
GO
CREATE INDEX MyTable_Index1 ON MyTable(MyPassword)
GO
INSERT INTO MyTable VALUES('password')
GO
SET SHOWPLAN_ALL ON
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
GO
--index scan
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' COLLATE French_BIN
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' AND
MyPassword = 'PASSWORD' COLLATE French_BIN
GO
SET SHOWPLAN_ALL OFF
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ersWOBtMGHA.1192@.TK2MSFTNGP11.phx.gbl...
> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
>
> SELECT ...
> WHERE MyColumn = MyData COLLATE FrenchBIN
> A +
> Dan Guzman a crit :
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle m
y
way through it that way if necessary, but is there a way to do this in
Enterprise Manager? Can I do this through the "Design Table" atributes?
Thanks,
John Steen
"John Steen" wrote:

> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they
are
> case sensitive?
> Thanks in advance.
> John Steen
>|||On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
<moderndads(nospam)@.hotmail.com> wrote:

>Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle
my
>way through it that way if necessary, but is there a way to do this in
>Enterprise Manager? Can I do this through the "Design Table" atributes?
Hi John,
Yes, you can. Click the column, go to the lower pane and change the
entry in the field "Colaltion". You can click the "..." button to the
right of this field to get an assisted dialog.
But you should learn how to write SQL for this - it gives you so much
more control! (And Enterprise Manager uses some REALLY inefficient ways
to make some changes...)
Hugo Kornelis, SQL Server MVP|||Thanks, Hugo. And you're right, I do need to learn to write more than simpl
e
SQL scripts. And as soon as I find the time... :-/
John Steen
"Hugo Kornelis" wrote:

> On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
> <moderndads(nospam)@.hotmail.com> wrote:
>
> Hi John,
> Yes, you can. Click the column, go to the lower pane and change the
> entry in the field "Colaltion". You can click the "..." button to the
> right of this field to get an assisted dialog.
> But you should learn how to write SQL for this - it gives you so much
> more control! (And Enterprise Manager uses some REALLY inefficient ways
> to make some changes...)
> --
> Hugo Kornelis, SQL Server MVP
>

Case Sensitive Columns

Setup: SQL Server 2000 Standard
I have a column that contains passwords that are made up of a mix of
uppercase and lowercase letters and numbers. They're supposed to be case
sensitive, but aren't. How can I change the column settings so that they are
case sensitive?
Thanks in advance.
John SteenIt appears your database default collation is case-insensitive. You can
change the collation for a specific column to a case-sensitive one with
ALTER TABLE:
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
INSERT INTO MyTable VALUES('password')
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
ALTER TABLE MyTable
ALTER COLUMN MyPassword varchar(10)
COLLATE SQL_Latin1_General_CP1_CS_AS
--row not found
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
--row found
SELECT *
FROM MyTable
WHERE MyPassword = 'password'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"John Steen" <moderndads(nospam)@.hotmail.com> wrote in message
news:CE9A1A9F-36C1-4E9E-90AE-BD1092912E7E@.microsoft.com...
> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they
> are
> case sensitive?
> Thanks in advance.
> John Steen
>|||You can also change the collation on the fly with the collate clause in
any SQL experssion in a stattement :
SELECT ...
WHERE MyColumn = MyData COLLATE FrenchBIN
A +
Dan Guzman a écrit :
> It appears your database default collation is case-insensitive. You can
> change the collation for a specific column to a case-sensitive one with
> ALTER TABLE:
> CREATE TABLE MyTable
> (
> MyPassword varchar(10)
> )
> INSERT INTO MyTable VALUES('password')
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> ALTER TABLE MyTable
> ALTER COLUMN MyPassword varchar(10)
> COLLATE SQL_Latin1_General_CP1_CS_AS
> --row not found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'PASSWORD'
> --row found
> SELECT *
> FROM MyTable
> WHERE MyPassword = 'password'
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
That's another method that to achieve the desired result but a consideration
is that the expression won't be sargable due to the different collations.
However, one can include criteria for both collations in the WHERE clause so
that indexes can be efficiently used. The example below shows how one can
use the technique. This might not be an issue in the OP's case since it is
likely that the actual WHERE clause also includes other indexed criteria
like UserID.
CREATE TABLE MyTable
(
MyPassword varchar(10)
)
GO
CREATE INDEX MyTable_Index1 ON MyTable(MyPassword)
GO
INSERT INTO MyTable VALUES('password')
GO
SET SHOWPLAN_ALL ON
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD'
GO
--index scan
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' COLLATE French_BIN
GO
--index seek
SELECT *
FROM MyTable
WHERE MyPassword = 'PASSWORD' AND
MyPassword = 'PASSWORD' COLLATE French_BIN
GO
SET SHOWPLAN_ALL OFF
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ersWOBtMGHA.1192@.TK2MSFTNGP11.phx.gbl...
> You can also change the collation on the fly with the collate clause in
> any SQL experssion in a stattement :
>
> SELECT ...
> WHERE MyColumn = MyData COLLATE FrenchBIN
> A +
> Dan Guzman a écrit :
>> It appears your database default collation is case-insensitive. You can
>> change the collation for a specific column to a case-sensitive one with
>> ALTER TABLE:
>> CREATE TABLE MyTable
>> (
>> MyPassword varchar(10)
>> )
>> INSERT INTO MyTable VALUES('password')
>> --row found
>> SELECT *
>> FROM MyTable
>> WHERE MyPassword = 'PASSWORD'
>> ALTER TABLE MyTable
>> ALTER COLUMN MyPassword varchar(10)
>> COLLATE SQL_Latin1_General_CP1_CS_AS
>> --row not found
>> SELECT *
>> FROM MyTable
>> WHERE MyPassword = 'PASSWORD'
>> --row found
>> SELECT *
>> FROM MyTable
>> WHERE MyPassword = 'password'
>
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************|||Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle my
way through it that way if necessary, but is there a way to do this in
Enterprise Manager? Can I do this through the "Design Table" atributes?
Thanks,
John Steen
"John Steen" wrote:
> Setup: SQL Server 2000 Standard
> I have a column that contains passwords that are made up of a mix of
> uppercase and lowercase letters and numbers. They're supposed to be case
> sensitive, but aren't. How can I change the column settings so that they are
> case sensitive?
> Thanks in advance.
> John Steen
>|||On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
<moderndads(nospam)@.hotmail.com> wrote:
>Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle my
>way through it that way if necessary, but is there a way to do this in
>Enterprise Manager? Can I do this through the "Design Table" atributes?
Hi John,
Yes, you can. Click the column, go to the lower pane and change the
entry in the field "Colaltion". You can click the "..." button to the
right of this field to get an assisted dialog.
But you should learn how to write SQL for this - it gives you so much
more control! (And Enterprise Manager uses some REALLY inefficient ways
to make some changes...)
--
Hugo Kornelis, SQL Server MVP|||Thanks, Hugo. And you're right, I do need to learn to write more than simple
SQL scripts. And as soon as I find the time... :-/
John Steen
"Hugo Kornelis" wrote:
> On Thu, 16 Feb 2006 07:48:30 -0800, "John Steen"
> <moderndads(nospam)@.hotmail.com> wrote:
> >Thanks, guys, but I'm pretty weak when it comes to scripting. I'll muddle my
> >way through it that way if necessary, but is there a way to do this in
> >Enterprise Manager? Can I do this through the "Design Table" atributes?
> Hi John,
> Yes, you can. Click the column, go to the lower pane and change the
> entry in the field "Colaltion". You can click the "..." button to the
> right of this field to get an assisted dialog.
> But you should learn how to write SQL for this - it gives you so much
> more control! (And Enterprise Manager uses some REALLY inefficient ways
> to make some changes...)
> --
> Hugo Kornelis, SQL Server MVP
>

Case in Inner Join? SQL statement help!!

Hi,

I have a 2 different telephone number tables,

Table 1 has some numbers beginning with '0' i.e. 08001234567 and some without the '0' i.e 8001234567

on my Table2 i only have numbers starting with '0' i.e 08001234567.

I would like to make a INNER JOIN statement and check if the telephone number dont have a starting '0', then append to it and try to do the join so I get both set of data.

for example (this doesnt work however...):

SELECT *

FROM dbo.Calls INNER JOIN
ON

CASE WHEN SUBSTRING(dbo.CallData.TelephoneNumber, 0, 1) = '0'

THEN dbo.Calls.TelephoneNumber = dbo.Post.TelephoneNumber

ELSE '0' & dbo.Calls.TelephoneNumber = dbo.Post.TelephoneNumber <--append a 0 at the start

END CASE

AND dbo.Products.FK_Client = dbo.CallDataSets.FK_Client
GROUP BY dbo.CallDataSets.FK_Client

I hope you understand what I am trying to achieve here...

Any help is appreciated!

Many thanks,

Jon

Try something like this:

Code Snippet

SELECT *
FROM dbo.Calls c
JOIN ...
ON ...
JOIN Post p
ON ( right(( '0' + c.TelephoneNumber ), 11 )) = ( right(( '0' + p.TelephoneNumber ), 11 ))
JOIN Products pr
ON ...

You only need to add the leading zero on the table that requires it -both are used above for illustration.

|||

thanks for the reply.

but the reason i need the CASE STATEMENT is that some of them don't have Zeros, and it wont join the table.

I want both numbers to be join with.

many thanks.

|||

So, did you even try the suggestion I sent.

If you did, what happened?

I have a 2 different telephone number tables,

Table 1 has some numbers beginning with '0' i.e. 08001234567 and some without the '0' i.e 8001234567

on my Table2 i only have numbers starting with '0' i.e 08001234567.

And of course, you could choose to correct the data that doesn't have a leading zero with an UPDATE statement like this (then both tables would have data in the same form):

Code Snippet

UPDATE Table1
SET TelephoneNumber = ( '0' + TelephoneNumber )
WHERE len( TelephoneNumber ) = 10

(or)

...
WHERE left( TelephoneNumber, 1 ) <> '0'

|||

thanks!

in the end is use CAST(object as bigint) to get rid or the zero.

|||That would also work just as well.