Parse A column containing SYntax

Has anyone tried to parse a column containing sql syntax to obtain the actual column names used in the syntax field. So if the field had acctno =1001 then it would return acctno.http://www.dbforums.com/showthread.php?t=1196943|||I haven't seen a script to do this, and it would be quite a challenge.

Parse fields

Hi there,

I am re-organizing the database. We used to have field 'names' in our
table for our first name and last name. However, I want to have those
names in different field.

FYI, I have two different tables in different databases. The A
database contains A table with 'names" field. The B database contains
B table with 'fname' and 'lname' I want to copy data A table to B
table.

How can I parse names field into first name and last name fields?

Here are some examples which are in the database.
(
id names
01 John Doe
02 John & Jane Doe
03 Mr & Mrs Doe
) something like this. It might contain '&' and two names. If there
are two names, then use first name.

Thanks guys,Do something like this
declare @.tbla table ( id int ,names varchar(100))
insert into @.tbla values (1,'John Doe')
insert into @.tbla values (1,'John & Jane Doe')
insert into @.tbla values (1,'Mr & Mrs Doe ')

select
id,
firstname = case when names like '%&%' then names
else
case when charindex(' ',names) 0
then substring(names,1,charindex(' ',names) - 1)
else names
end
end ,
lastname = case when names like '%&%' then ''
else
case when charindex(' ',names) 0
then substring(names,charindex(' ',names)+ 1,len(names) -
charindex(' ',names))
else names
end
end

from @.tbla

M A Srinivas

On Mar 9, 1:20 pm, "portCo" <woos...@.gmail.comwrote:

Quote:

Originally Posted by

Hi there,
>
I am re-organizing the database. We used to have field 'names' in our
table for our first name and last name. However, I want to have those
names in different field.
>
FYI, I have two different tables in different databases. The A
database contains A table with 'names" field. The B database contains
B table with 'fname' and 'lname' I want to copy data A table to B
table.
>
How can I parse names field into first name and last name fields?
>
Here are some examples which are in the database.
(
id names
01 John Doe
02 John & Jane Doe
03 Mr & Mrs Doe
) something like this. It might contain '&' and two names. If there
are two names, then use first name.
>
Thanks guys,

|||On Mar 9, 10:46 am, masri...@.gmail.com wrote:

Quote:

Originally Posted by

Do something like this
declare @.tbla table ( id int ,names varchar(100))
insert into @.tbla values (1,'John Doe')
insert into @.tbla values (1,'John & Jane Doe')
insert into @.tbla values (1,'Mr & Mrs Doe ')
>
select
id,
firstname = case when names like '%&%' then names
else
case when charindex(' ',names) 0
then substring(names,1,charindex(' ',names) - 1)
else names
end
end ,
lastname = case when names like '%&%' then ''
else
case when charindex(' ',names) 0
then substring(names,charindex(' ',names)+ 1,len(names) -
charindex(' ',names))
else names
end
end
>
from @.tbla
>
M A Srinivas
>
On Mar 9, 1:20 pm, "portCo" <woos...@.gmail.comwrote:
>
>
>

Quote:

Originally Posted by

Hi there,

>

Quote:

Originally Posted by

I am re-organizing the database. We used to have field 'names' in our
table for our first name and last name. However, I want to have those
names in different field.

>

Quote:

Originally Posted by

FYI, I have two different tables in different databases. The A
database contains A table with 'names" field. The B database contains
B table with 'fname' and 'lname' I want to copy data A table to B
table.

>

Quote:

Originally Posted by

How can I parse names field into first name and last name fields?

>

Quote:

Originally Posted by

Here are some examples which are in the database.
(
id names
01 John Doe
02 John & Jane Doe
03 Mr & Mrs Doe
) something like this. It might contain '&' and two names. If there
are two names, then use first name.

>

Quote:

Originally Posted by

Thanks guys,- Hide quoted text -

>
- Show quoted text -

The following will also work:

SELECTid,
names,
RTRIM(SUBSTRING(names, 1, CHARINDEX(' ', names) - 1)) fname,
CASE
WHEN CHARINDEX('&', names) 0 THEN
LTRIM(SUBSTRING(names, CHARINDEX('&', names) + 1, LEN(names)))
ELSE
LTRIM(SUBSTRING(names, CHARINDEX(' ', names), LEN(names)))
END lname
FROMtblA|||On Mar 9, 12:20 am, "portCo" <woos...@.gmail.comwrote:

Quote:

Originally Posted by

Hi there,
>
I am re-organizing the database. We used to have field 'names' in our
table for our first name and last name. However, I want to have those
names in different field.
>
FYI, I have two different tables in different databases. The A
database contains A table with 'names" field. The B database contains
B table with 'fname' and 'lname' I want to copy data A table to B
table.
>
How can I parse names field into first name and last name fields?
>
Here are some examples which are in the database.
(
id names
01 John Doe
02 John & Jane Doe
03 Mr & Mrs Doe
) something like this. It might contain '&' and two names. If there
are two names, then use first name.
>
Thanks guys,

Thanks guys|||>How can I parse names field [sic] into first name and last name fields [sic]? <<

Don't do it. Name handling is an ugly problem and if you have to do
this on a regular basis get a package designed for this kind of work.
Some companies are Group 1 Software, SSA (used to have a great booklet
on this topic), Melissa Data Corporation and Promark Software Inc.

Their software handles mailing lists and you can get a review copy
from Melissa Data. They do not choke on names like "John Paul van der
Poon" and worse.|||On Mar 9, 2:20 pm, "portCo" <woos...@.gmail.comwrote:

Quote:

Originally Posted by

Hi there,
>
I am re-organizing the database. We used to have field 'names' in our
table for our first name and last name. However, I want to have those
names in different field.
>
FYI, I have two different tables in different databases. The A
database contains A table with 'names" field. The B database contains
B table with 'fname' and 'lname' I want to copy data A table to B
table.
>
How can I parse names field into first name and last name fields?
>
Here are some examples which are in the database.
(
id names
01 John Doe
02 John & Jane Doe
03 Mr & Mrs Doe
) something like this. It might contain '&' and two names. If there
are two names, then use first name.
>
Thanks guys,

hi,

select id,
fname = substring(ltrim(rtrim(names)),1,charindex('
',ltrim(rtrim(names))) - 1),
lname = reverse(substring(reverse(ltrim(rtrim(names))),
1,charindex(' ',reverse(ltrim(rtrim(names)))) - 1))
from Atable

HTH

parametrised servername and catalog name

Hi,

I intend to use four-part table names to select data from a Linked Server into local tables. There will be stored procedures containing Insert-Select statements.

While I'm developing, I'm pointing to a development version of the remote server. In production, the remote server will be different. There will be many other situations, where I will need to link to one remote server or another. But I don't want to recompile my stored procs every time.

The question is: can I use variables for the first two parts of a four-part table name. Something like:

declare @.svr varchar(20)
declare @.dsn varchar(20)
select @.svr = 'Pervasive_Test', @.dsn = 'D_drive'
SELECT * from @.svr.@.dsn..remote_table

Would the above work ?

I have to go through the ODBC Provider. The only way my query works is when I also specify the DSN as "catalog" in the second position of the table name. I definitely do not want the name of the DSN to be hardwired into my stored procs.

Andrewyour example wont work, though you can use
sp_executesql (see BOL) to run commands with variable object names,

eg.

set @.statement = N'select user_id from users
where cc_number = '+''''+ltrim(rtrim(@.cc_number))+''''

insert into #master_id (user_id)
exec sp_executesql @.statement

though for the sort of thing you are doing, I usually create a batch file which uses a tool like SED to replace each token (defined earlier on object names) with the desired values , and then use the relevant modified script for each installation...just have the bat file receive the server/tablname as params

Parameters as Column names

I have a simple query but I need the parameter to select the column name
of the WHERE clause.
SELECT col1, col2, col3, col4 FROM Table WHERE @.colname = 1
The query builder is changing this everytime I set a parameter as a column
name. Is there a way to do this?I figured out my problem. I made the query a dynamic query by making it:
="SELECT col1, col2, col3, col4 FROM Table WHERE " & Parameters!Colname.Value
& " = 1"
I was running into all sorts of errors, but I found out that the query needs
to be all on one line with no linebreaks.
> I have a simple query but I need the parameter to select the column
> name of the WHERE clause.
> SELECT col1, col2, col3, col4 FROM Table WHERE @.colname = 1
> The query builder is changing this everytime I set a parameter as a
> column name. Is there a way to do this?
>

Parameters - Selection from a long list

Hello

I want to have a report query parameter which has many thousands of possible values (i.e. Customer Names). I need to provide the ability to allow a user to search for the customer they want and then have that customer's detail appear in the parameter.

Ideally I would like something like the ability to have a "Find" button next to the parameter that would kick off a "Find" report and then pass the value selected back to the original parameter.

The parameter in question is one of a numvber (6) on the actual report so have discounted the option of creating a "Find Customer" report as step 1 and then passing the selection forward to the actual report (Step 2) i.e. drill through.

Any thoughts/suggestions would be much appreciated as I know I will have many of these scenarios in the coming months as I will be using numerous parameters with large selection lists.

Thanks in advance

Eliot

No user is going to want a maseeev ddlist to scroll through.

Has anyone ever tried using code to do an ajax/google suggest type thing?

If so please post!

I think you are going to have to use cascading parameters

So free entry text param 1 allows the user to enter some of the name

param 2 is driven by this and does some sort of fuzzy matching to return a list of possible candidates LIKE 'param1' + '%' (soundex also might help)

|||

Thanks for your reply.

Using Cascading parameters will work in some cases but not in this particular situation as I need to provide multiple ways to search for a customer. i.e. Surname, Customer Number, Postcode etc.

However, some of my scenarios could use cascading parameters.

Any other thoughts out there?