Excludes 'Z' while giving LIKE '[A-Z]%' but works fine for all other combinations

Hai Friends!

I have a peculiar problem while using LIKE pattern match with SQL Server 2005. My requirement is to list names starting from a range of Characters (anything from a to z). If I want to list Names starting from A to Z I am giving the LIKE pattern as LIKE '[A-Z]%' but the result set contains names starting with 'A' alone. If I give '[B-Z]%' the 'B' Alone is included in the result set. For all remaining combinations (ex: '[A-Y]%') it gives correct results.

If I give '%' or 'Z%' it gives all starting with Z correctly. If I include the Z with any other alphabet it takes only the starting alphabet and excludes all remaining letters.

This problem seems to be too strange and couldn't understand why it gives wrong results!!

Any solutions? Thanks in advance

Visualcpp

Please list your specific syntax; the following works fine:

select aName
from ( select 'Alphabet' as aName
union all
select 'Zymurgy'
) a
where aName like '[A-Z]%'

-- aName
-- --
-- Alphabet
-- Zymurgy

Excel to MS SQL

Hai all,
I want to export the data from Excel sheet to SQL database. In the Excel sheet one column contains the date value and non date like 0 and blank. I want to transfer this to my table by changing the format to dd/mm/yyyy.

If I open and format the column to date, and if I export then in the table i am getting different formats ( based on the client machines Date format , The column in the table is Varchar becaust the Excel sheet column will contain date and other non date like 0 , blank etc )

Now how can I export the Excel column that contains date , 0 and blanks to the table in the database with date format dd/mm/yyyy and null for non date values?

Thanks
NarayanaswamyYou should import your data as VARCHAR in a temporary table, and transform it in a second step.|||Hai ,
I tried that also, but the problem is
If In the excel sheet first field is a text or 0 then All thetext values are transfered to the Table but the date fields are transfered as NULL.
If the first row in the excel is Date then All the date are transferred properly and the text and 0 are transfered as NULL.

But the date transfed is of various type ( as per the client machine Date format how can i convert it to same format?)

Can you help me in solving this problem?

Thanks

Narayanaswamy|||Yeah, Excel is great, but not for storing data.

I would take another approach, and would include some VBA code into your workbooks to transform your sheets first into the proper format before exporting them. Your could also consider to export them by VBA.|||Originally posted by Narayanaswamy
Hai ,
I tried that also, but the problem is
If In the excel sheet first field is a text or 0 then All thetext values are transfered to the Table but the date fields are transfered as NULL.
If the first row in the excel is Date then All the date are transferred properly and the text and 0 are transfered as NULL.

But the date transfed is of various type ( as per the client machine Date format how can i convert it to same format?)

Can you help me in solving this problem?

Thanks

Narayanaswamy

I did have the same problem with importing excel data to MSSQL.
After some fighting I just imported data to Access and then to MSSQL.
It was needed to do not often. :)

Excel Sheet name and Sorting Problem...

Hai,
Is it possible to give another name that "Sheet1" when I export my
report to Excel ?
Also
When i make report header it is not allowing me to sort?
Any solutions/Samples ?
Thanks in advance
Aravind.SNot sure about the Sheet1 name, but when you create a report header, make
sure that the end of the header text box lines up with the end of a text box
in your detail.
Hope that helps!
"aravindsabapathy@.gmail.com" wrote:
> Hai,
> Is it possible to give another name that "Sheet1" when I export my
> report to Excel ?
> Also
> When i make report header it is not allowing me to sort?
> Any solutions/Samples ?
> Thanks in advance
> Aravind.S
>