|
Try this
declare @t table(Company_Name varchar(50))
insert into @t
select 'ComxxxA Co.' union all select 'ComxxxA Co.' union all
select 'ComxxxA Co.,Ltd.' union all select 'ComxxxA Co.,Ltd.' union all
select' ComxxxA Co.,' union all select 'ComxxxA Co.,' union all
select 'ComxxxA ' union all select 'ComxxxA ' union all
select 'Comx' union all select 'ComxxxxxxxB Co.,Ldt' union all
select 'ComxxxxxxxB Co.,Ldt' union all select 'ComxxxxxxxB Co.,Ldt' union all
select 'ComxxxxxxxB Co.,Ldt' union all select 'ComxxxxxxxB Co' union all
select 'ComxxxxxxxB Co.,' union all select 'ComxxxxxxxB Co.,'
Query:
select Company_Name
from @t
where replace(Company_Name, ' ','') not like '%[.,]%'
group by Company_Name
Output:
Company_Name
Comx
ComxxxA
ComxxxxxxxB Co
Niladri Biswas
|
|
|
|
|
Thanks,
Yes it like example.
But it is not enough yet.
Because in this field has more companies like that
up to differenc user enter difference.
So have any solution for protect it ?
VB.Net
|
|
|
|
|
|
it is not complete what i want.
but i would like to thank to you so much.
VB.Net
|
|
|
|
|
Hi,
you can use SELECT DISTINCT[^] and ORDERBY to get an ordered list of all different company names present.
you can't find the "shortest" names as in your example, it would only give "Comx", not what you said it would.
|
|
|
|
|
Hi.
I wrote some VBA underlying code for an Access 2000 application and used to create/open recordsets in the following manner:
Dim rstLookupFixedAreas As New ADODB.Recordset
rstLookupFixedAreas.Open "tbl_FixedAreas", CurrentProject.Connection, adOpenStatic, adLockOptimistic
rstLookupFixedAreas.MoveFirst
Do Until (rstLookupFixedAreas.EOF)
strValueList = strValueList & CStr(rstLookupFixedAreas.Fields(0)) & ";" & rstLookupFixedAreas.Fields(1) & ";"
rstLookupFixedAreas.MoveNext
Loop
comboFixedArea.RowSource = strValueList
'comboFixedArea.DefaultValue = 1
rstLookupFixedAreas.Close
I did this by including the reference to Microsoft ActiveX Data Object 2.1 Library.
Now that I am using Access 2007, is there a more current method to do the same type of "recordset" operations? (or should i continue with including ADO 2.1 Library)
I want to use either a record index or Find method to get my record pointer.
Thank you for the help.
John John
|
|
|
|
|
I have a .csv file with following column:
Col1,Col2
And the data table has one extra datetime column:
Col1,Col2,TimeStamp
While importing data using BULK INSERT from the CSV, is there a way to specify the value for the TimeStamp column too? (Timestamp would be same for all the newly imported rows)
Suhredayan
|
|
|
|
|
How about defining a default value for the column?
ALTER TABLE dbo.tablename ADD CONSTRAINT
DF_TimeStamp DEFAULT '20091218' FOR TimeStamp
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Ashfield wrote: How about defining a default value for the column?
That may not work since the timestamp is same only per bulk insert. I do have a solution for this, wrap bulk insert inside a stored proc, and update the the timestamp from SP. But this would introduce few additional steps, was wondering if this can be avoided in case if BULK INSERT already has this feature.
Thank you,
Suhredayan
|
|
|
|
|
I have a time series data in which a numerical indicator fluctuates over time. If I want to find out for which given day, this indicator has crossed above 30 in the past 3 days. Is it possible to write a SQL query that retrieve this information?
The logic to check for the crossing movement is:
IF indicator(day x) > 30 AND indicator(3 days ago from day x) <30 THEN
Return "YES WE HAVE AN UPWARD CROSSING MOVEMENT THROUGH 30 FOR PAST 3 DAYS!"
END IF
Can this be accomplished using a SQL CASE statement with a HAVING clause or something else?
I'd greatly appreciate any help! If possible please provide a sample code
|
|
|
|
|
An exists should do it - as far as I can tell from your description
select column_list....
from table1 t1
where indicator > 30
and exists (select 1 from table1 t2 where t2.pk = t1.pk and t2.indicator < 30 and t2.dateadded = dateadd(day,-3,t1.dateadded))
where PK is your primary key
[Edit] I had missed the date bit from the exists originally [/Edit]
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
modified on Tuesday, December 15, 2009 11:17 AM
|
|
|
|
|
Hi Ashfield, Your code looks exactly like what I need! I'll give it a try after getting home.
Many thanks again.
modified on Tuesday, December 15, 2009 10:19 PM
|
|
|
|
|
no problem
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Please do not cross post.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Thanks for your help in the other section, sorry about cross-posting. Will try to prevent that in the future.
|
|
|
|
|
Hi everyone,
my table is
6219 HBO 02 0
6220 HBO1 0201 6219
6221 HEAO 0202 6219
6222 HTS 0203 6219
6231 MEAO 0403 6228
6232 MTS 0404 6228
6234 Midde 05 0
6235 Overi 06 0
6228 MBO 04 0
6236 WO 10 0
6237 Post 07 0
6239 Basi NULL NULL
6448 Athe 05001 6234
but i need like this,
6219 HBO 02 0
6220 HBO1 0201 6219
6221 HEAO 0202 6219
6222 HTS 0203 6219
6228 MBO 04 0
6231 MEAO 0403 6228
6232 MTS 0404 6228
6234 Midde 05 0
6448 Athe 05001 6234
6235 Overi 06 0
6236 WO 10 0
6237 Post 07 0
6239 Basi NULL NULL
Nothing is Impossible. Keep always Smiling...
|
|
|
|
|
|
that is all child records should be displayed next to parent.
Nothing is Impossible. Keep always Smiling...
|
|
|
|
|
If you tell us that the fourth field is the parent's ID (and the first field is its ID), then we will have an easier time trying to figure out what you want.
modified on Tuesday, December 15, 2009 10:57 AM
|
|
|
|
|
Try this.
Inputs:
declare @t table(childid int,name varchar(20),code int,parentid int)
insert into @t values (6219,'HBO',02,0)
insert into @t values(6220,'HBO1', 0201,6219)
insert into @t values(6221,'HEAO',0202,6219)
insert into @t values(6222,'HTS',0203,6219)
insert into @t values(6231,'MEAO',0403,6228)
insert into @t values(6232,'MTS',0404,6228)
insert into @t values(6234,'Midde',05,0)
insert into @t values(6235,'Overi',06,0)
insert into @t values(6228,'MBO',04,0)
insert into @t values(6236,'WO',10,0)
insert into @t values(6237,'Post',07,0)
insert into @t values(6239,'Basi',NULL,NULL)
insert into @t values(6448,'Athe',05001,6234)
select * from @t
Query:
;with cte as
(
select
cast(t1.childid as varchar(1000)) [path]
, t1.childid
,t1.name
,t1.code
,t1.parentid
,0 AS [Level] from @t t1 where parentid = 0 or parentid is null
union all
select
cast([path] + '/' + cast(t1.childid as varchar(1000)) as varchar(1000)) [path]
,t1.childid
,t1.name
,t1.code
,t1.parentid
,c.[Level]+1 AS [Level]from @t t1
join cte c
on c.childid = t1.parentid
)
select childid,name,code,parentid
from cte order by [path]
Output:
childid name code parentid
6219 HBO 2 0
6220 HBO1 201 6219
6221 HEAO 202 6219
6222 HTS 203 6219
6228 MBO 4 0
6231 MEAO 403 6228
6232 MTS 404 6228
6234 Midde 5 0
6448 Athe 5001 6234
6235 Overi 6 0
6236 WO 10 0
6237 Post 7 0
6239 Basi NULL NULL
Niladri Biswas
|
|
|
|
|
Thanks for ur timely help...
Nothing is Impossible. Keep always Smiling...
|
|
|
|
|
Hi!
Can someone please tell me how would I go about creating a Stored Proc to select records between dates?
I have 2 variables, date1 and date2 I want to create a stored proc as follows:
select * from table between date1 and date2 where the dates are variables in the stored proc.
Illegal Operation
|
|
|
|
|
select * from table
where datecolumn between date1 and date2
e.g.
declare @t table(activity varchar(10),datecol datetime)
insert into @t
select 'activity1', '2009-12-01' union all
select 'activity2','2009-12-02' union all
select 'activity3','2009-12-15' union all
select 'activity4','2010-01-02' union all
select 'activity5','2009-12-31'
Declare 2 date variables
declare @date1 datetime,@date2 datetime
set @date1 = '2009-12-01'
set @date2 = '2009-12-15'
I want to select records between 1st Dec 2009 to 15th Dec 2009.
Query:
select * from @t
where datecol between @date1 and @date2
Output:
activity datecol
activity1 2009-12-01 00:00:00.000
activity2 2009-12-02 00:00:00.000
activity3 2009-12-15 00:00:00.000
Niladri Biswas
|
|
|
|
|
CREATE PROCEDURE usp_GetDataFromDateRange(
@Start DATETIME,
@End DATETIME
)
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM MyTable
WHERE DateCol BETWEEN @Start AND @End
RETURN
END
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
i have table A with field password varchar(15), am migtrating the data to password in another table datatype varbinary with script component transformation. i set the password to byte stream
i have this code to encrypt the password so i can store it as varbinary
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Try
Dim hashBytes As Byte()
Dim encoding As New UnicodeEncoding
hashBytes = encoding.GetBytes(Row.app)
Dim sha As New SHA1CryptoServiceProvider
Dim crpytPassword As Byte() = sha.ComputeHash(hashBytes)
Row.passout = crpytPassword
Catch ex As Exception
End Try
End Sub
but am getting this error
[AccountProfile New Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
[AccountProfile New Destination [16]] Error: There was an error with input column "pass" (88) on input "OLE DB Destination Input" (29). The column status returned was: "The value could not be converted because of a potential loss of data.".
thanks
|
|
|
|
|