Re: Data which includes foregin characters - Database Discussion Boards

Re: conver string to date not in date time format

Ashfield12-May-08 23:36

12-May-08 23:36

Well exactly what IS your requirement? As far as I know, myself and others have answered your original question (such as it was)

trilokharry wrote:
like I want to convert this string(15 may 2008) '15/05/2008' in date format.

As a final suggestion, try this.

select * from table where CONVERT(VARCHAR(20),vardate,106) = '15 may 2008'

Otherwise explain EXACTLY what you want. The database does not really care what format your date is in as it stores it as a number anyway.

Bob
Ashfield Consultants Ltd

Re: conver string to date not in date time format

trilokharry13-May-08 0:50

trilokharry

13-May-08 0:50

thanks
now it's working fine

You get the best out of others when you give the best of yourself.

Re: conver string to date not in date time format

Blue_Boy13-May-08 0:59

Blue_Boy

13-May-08 0:59

In his first post I was confused and I didn't figure out what excatly he wants.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

Re: conver string to date not in date time format

Ashfield13-May-08 1:56

Ashfield

13-May-08 1:56

No, I was basing my answers mainly on guesswork Smile | :)

Bob
Ashfield Consultants Ltd

Data which includes foregin characters

Naveed Kamboh12-May-08 1:43

Naveed Kamboh

12-May-08 1:43

I've got data in English but that contains Some French characters too.
for example

Actual data
The Coleg Sir Gâr
Sabhal Mòr Ostaig
Château du Rosey
Areté
Ecole La Découverte
Colegio Gran Bretaña
St Thérèse of Lisieux RC Primary School

Search input would be:
The Coleg Sir Gar (â replaced by a (English))
In data, anywhere there could bey a foregin character and that could be any foreign character How can I included these records in my Search results Using SQL or T-SQL. I am using SQL server 2005 standard.
any help will bhe appreciated.

Naveed Kamboh
Complexity kills, Write easy code for your self.
Punjabi stuff

Re: Data which includes foregin characters

Ashfield12-May-08 2:01

Ashfield

12-May-08 2:01

You need to define your columns as nvarchar, then they will accept these characters.

Bob
Ashfield Consultants Ltd

Re: Data which includes foregin characters

Naveed Kamboh12-May-08 2:05

Naveed Kamboh

12-May-08 2:05

You didn't get my question I gugess.
I have already that data in varchar there 10,000+ records which containt these foreign characters.
When we search using english chracters they do not come in the search. So what would be the solution to show them in the search
while using english characters as someone will type Foregin chracters.

Naveed Kamboh
Complexity kills, Write easy code for your self.
Punjabi stuff

Re: Data which includes foregin characters

Ashfield12-May-08 2:59

Ashfield

12-May-08 2:59

Naveed Kamboh wrote:
I have already that data in varchar there 10,000+ records which containt these foreign characters.
When we search using english chracters they do not come in the search.

In which case I think you are out of luck. If you think about it logically the characters are not the same, the ascii values of the characters differ so the strings differ.

Bob
Ashfield Consultants Ltd

Re: Data which includes foregin characters

Blue_Boy12-May-08 2:58

Blue_Boy

12-May-08 2:58

try to use SOUNDEX.
More info about SOUNDEX.[^]

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

Re: Data which includes foregin characters

Naveed Kamboh12-May-08 3:04

Naveed Kamboh

12-May-08 3:04

SoundEx doesn't work.
I've tried that

Naveed Kamboh
Complexity kills, Write easy code for your self.
Punjabi stuff

Re: Data which includes foregin characters /found solution

Naveed Kamboh12-May-08 3:51

Naveed Kamboh

12-May-08 3:51

I found solution here
http://www.sqlservercentral.com/Forums/Topic498678-338-1.aspx?Update=1[^]

Naveed Kamboh
Complexity kills, Write easy code for your self.
Punjabi stuff

Re: Data which includes foregin characters /found solution

Ashfield12-May-08 3:53

Ashfield

12-May-08 3:53

Just goes to show, posting to multiple forums can help. Nice one.

Bob
Ashfield Consultants Ltd

Re: Data which includes foregin characters

its.rajendra12-May-08 21:04

its.rajendra

12-May-08 21:04

Hi Naveed,
In case of multilingual, we shud follow nvarchar always...after that you can search everything using N'%" + SearcText + "%' .... so that it will be easy to find out the desired result.

Re: Data which includes foregin characters

Naveed Kamboh12-May-08 22:25

Naveed Kamboh

12-May-08 22:25

Can you please give me any example what do you mean
I tried following query

select organisationname from
organisation where
organisationname like N'%Arete%'

It is not returning me in results "Areté"

My actual stored procduer runs with following where clause
@WhereClause = N'((OrganisationName COLLATE SQL_Latin1_General_CP1_CI_AI ) LIKE '%arete%'
and its giving me results which wanted.

Naveed Kamboh
Complexity kills, Write easy code for your self.
Punjabi stuff

SQL Query statment syntex for how to stored output of select query to variabe

Sasmi_Office12-May-08 0:42

Sasmi_Office

12-May-08 0:42

Dear Friends,

i want to declare an float variable and stored data in that variable the scenario is as given below

suppose i have table name Table_1 having three column BranchID, Payment and Date
BranchID, Payment, Date
1, 2000, 10/05/2008
1, 1000, 11/05/2008
1, 3000, 12/05/2008
2, 2000, 10/05/2008

i want to create an procedure which give output as 'Branch ID 1 made payment of 6000 Rs' passing the parameter BranchID and Date

Parameter value

BranchID = 1 and Date BETWEEN CONVERT(DATETIME, '2008-05-10 00:00:00', 102) AND CONVERT(DATETIME, '2008-05-12 00:00:00', 102)

how can i do this please help me thanks in advanced Rose | [Rose]

Sasmi

Re: SQL Query statment syntex for how to stored output of select query to variabe

Blue_Boy12-May-08 1:33

Blue_Boy

12-May-08 1:33

Do you want to get all values of BranchID by sum of Payment is 6000 and between two dates?

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

Re: SQL Query statment syntex for how to stored output of select query to variabe

Sasmi_Office12-May-08 1:38

Sasmi_Office

12-May-08 1:38

I Want outut if my inpout is Branch ID = 1 and Date BETWEEN CONVERT(DATETIME,
'2008-05-06 00:00:00', 102) AND CONVERT(DATETIME, '2008-05-13 00:00:00', 102)

So it should be 6000 Rs which is sum of all record whose criteria satisfy the branch ID and Date as provided by user

Sasmi

making <null> inside a table??</null> [modified]

Karan_TN11-May-08 21:13

Karan_TN

11-May-08 21:13

currently i m using SQL server 2000.
i need a query to insert "NULL" inside my table wherever the field lenght=0.
help me! - KARAN

modified on Monday, May 12, 2008 4:44 AM

Re: making inside a table??

Ashfield11-May-08 23:27

Ashfield

11-May-08 23:27

Use a case statement to check the length -

case when datalength(value) = 0 then null else value end

Bob
Ashfield Consultants Ltd

Re: making inside a table??

Karan_TN12-May-08 0:52

Karan_TN

12-May-08 0:52

Thanks Bob......
But the thing is how to proceed.
I m having nearly 50 columns in my database and above 5000 records.......... how to do this using "CASE"?

i want to implement all the 50 columns one by one?

Re: making inside a table??

Ashfield12-May-08 1:27

Ashfield

12-May-08 1:27

There is no easy way in sql. If you are calling the insert from an application then perhaps you could do it within the code there, otherwise its a case of string the case statments together..

insert into table1(cola, colb,.....)<br />
select case when datalength(valuea) = 0 then null else valuea end, case when datalength(valueb) = 0 then null else valueb end  .....

Personally I would write some sql to generate the insert statement.

Hope this helps

Bob
Ashfield Consultants Ltd

Re: making inside a table??

Karan_TN12-May-08 22:59

Karan_TN

12-May-08 22:59

thanks yaar! could it handle using TRIGGERS? that is, after user update or insert, it has to check the inserted values and if column length is zero, insert null??? this can be done? - KARAN

How to calculate sum of count of cloumn?

GroCareer11-May-08 18:05

GroCareer

11-May-08 18:05

Hi I want to calculate sum after the count of the column.

e.g my query is :
Select Name,(Count(Value) as cnt from NameTable1 group by Name

result : Name cnt
Riya 2
Jiya 5
reema 4

I want to calcluate sum of count of value field that is :11

Plz let me query for this.

Thnx

Re: How to calculate sum of count of cloumn?

GroCareer11-May-08 18:33

GroCareer

11-May-08 18:33

Hi
I have caluculated sum 11 from

Select Sum(cnt)as cnt1 from
(Select Name,(Count(Count))as cnt from Nametable1 group by Name)a query

But i want output in one resultset as:

Name cnt cnt1
Riya 2 11
Jiya 5 11
reema 4 11

Plz help as soon as possible

thnx

Re: How to calculate sum of count of cloumn?

Alsvha11-May-08 20:32

Alsvha

11-May-08 20:32

Not sure if the following will do what you want:

SELECT Name, cnt, sum(cnt)
FROM (
SELECT Name, Count(Count)) as cnt FROM NameTable1 Group By Name
) AS InnerQuery
GROUP BY cnt, Name

---------------------------
127.0.0.1 - Sweet 127.0.0.1

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.