Check If Column Is Substring Of Another Column
May 29, 2008
I have two columns.
policyNumber contains a 12-13 varchar string
AllPolicyNumbersIncluded contains one or more 12-13 varchar strings (policy nums) seperated by commas
I want to check if policyNumber is contained in AllPolicyNumbersIncluded?
I have policyNumber LIKE AllPolicyNumbersIncluded which works when only one policy number is in AllPolicyNumbersIncluded and incidently works switched around AllPolicyNumbersIncluded LIKE policyNumber I assume because they are equal.
Can anyone tell me how to check if one column's value is a substring of another - without going through every possible substring of the second
View 11 Replies
ADVERTISEMENT
Oct 8, 2007
How can I test to see if a column exists before adding a column to a sql mobile table?
thanks,
Luis
View 1 Replies
View Related
May 23, 2007
I'm trying to create a function that splits up a column by spaces, andI thought creating a function that finds the spaces with CHARINDEX andthen SUBSTRING on those values would an approach. I get an errorsaying that the I have an Invalid column 'Course_Number'. Not surewhy but I am very new to User Defined Functions. Here is what I haveso far:CREATE FUNCTION CourseEvalBreakdown(@fskey int)RETURNS @CourseTable TABLE(Col CHAR(2),Area CHAR(4),Number CHAR(4),Section CHAR(4),Term CHAR(3))ASBEGINDECLARE@Ind1 tinyint,@Ind2 tinyint,@Rows intDECLARE @crstbl TABLE (FStaffKey int,Course_Number char(20),Term char(3),Col char(2),Area char(4),Number char(4),Section char(3))INSERT INTO @crstbl (FStaffKey, Course_Number, Term)SELECT FStaffKey, Course_Number, TermFROM EvalWHERE FStaffKey = @fskeySET @Rows = @@rowcountWHILE @Rows 0BEGINSET @Ind1 = CHARINDEX(' ', Course_Number, 4)SET @Ind2 = CHARINDEX(' ',Course_Number, (CHARINDEX(' ',Course_Number, 4)+1))UPDATE @crstblSET Col = SUBSTRING(Course_Number, 1, 2)WHERE FStaffKey = @fskeyUPDATE @crstblSET Area = UPPER(SUBSTRING(Course_Number, 4, @Ind1-4))WHERE FStaffKey = @fskeyUPDATE @crstblSET Number = UPPER(SUBSTRING(Course_Number, @Ind1+1, (@Ind2-@Ind1)-1))WHERE FStaffKey = @fskeyUPDATE @crstblSET Section = SUBSTRING(Course_Number, @Ind2+1, 3)WHERE FStaffKey = @fskeyENDINSERT @CourseTableSELECT Col, Area, Number, Section, Term FROM @crstblRETURNENDGO
View 7 Replies
View Related
Apr 6, 2007
I have a table which has a field called Org. This field can be segmented from one to five segments based on a user defined delimiter and user defined segment length. Another table contains one row of data with the user defined delimiter and the start and length of each segment. e.g.
Table 1
Org
aaa:aaa:aa
aaa:aaa:ab
aaa:aab:aa
Table 2
delim
Seg1Start
Seg1Len
Seg2Start
Seg2Len
Seg3Start
Seg3Len
:
1
3
5
3
9
2
My objective is to use SSIS and derive three columns from the one column in Table 1 based on the positions defined in Table 2. Table 2 is a single row table. I thought perhaps I could use the substring function and nest the select statement in place of the parameters in the derived column data flow. I don't seem to be able to get this to work.
Any ideas? Can this be done in SSIS?
I'd really appreciate any insight that anyone might have.
Regards,
Bill
View 23 Replies
View Related
Sep 15, 2006
Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.
I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.
Easy Peasy for you guys, any help would be appreciated.
View 6 Replies
View Related
Jul 12, 2007
I want to fin out the number of records that are not numeric on my database, how can I fin that using an SQL query?
View 2 Replies
View Related
May 20, 2004
Hello All,
Can someone pls help me with how to check for a numeric value in a varchar column?
For example I have a column called client_id , it has values "AB" , "CD" , "18", "19" . I need to delete those client_id where the values are 18 and 19. How would I do that?
Thanks in advance!!
View 2 Replies
View Related
Sep 21, 2004
Can't seem to get my head around this: I'm looking for a way to select only those varchar(10) values that soley consist of numbers. Leading spaces are allowed.
I tried using isnumeric, but it also allows those with periods, comma's etc.
Also tried using like, but the length of the varchar column varies too much to do a like '[0-9][0-9]....'.
As a solution I currently do a combo of isnumeric, not like '%.%', not like '%,%' etc. I need to do a conversion to an int to join another table, but the convert still fails. Not sure where and why.
I'm thinking there should be a better way than create a hughe list of "not like " but it looks like I'm in the woods here...
View 2 Replies
View Related
Apr 8, 2004
In a stored procedure I have I have dates in the format YYYYMMDD with symbols representing the first 3 digits
e.g. °30903 =20030903, and I have to convert them to proper dates, and then eliminate all old data, so I replace symbols and then convert to int
SELECT af.AccomType, af.AccomRef, af.AccomName,af.address1, af.address2, bf1.RoomCode,
Convert(Int,Replace(Replace(Replace(Replace(REPLAC E(Replace(MAX(bf1.EndBook),'°','200'),'´','204'),' 99','1999'),'97','1997'),'47',1947),'98','1998')) AS max_date,
...............
WHERE
af.Resort=@strResort
AND
(af.AccomType = 'H' OR af.AccomType = 'O')
AND
max_date>20040721
order by max_date.
Problem is I get an error saying invalid column max_date. It works in the order by clause when I get rid of the
'max_date>20040721 '.
Thanks
View 2 Replies
View Related
Mar 1, 2008
I am trying to query multiple columns for a specific value. I have 8 columns (values are either 1 or 0)and I want to query the table to find out which rows contain zero's in ALL of the 8 columns. Whats the best way to do this? I can create a lenghty select statement where column1 =0 and column2 =0 and column3=0 and column4 =0 and column5 =0 .... etc. I was wondering if there was an easier way to do this?
Thanks in Advance
Shankar.N
View 2 Replies
View Related
May 22, 2008
I have 5 to six id columns from my excel input.they can be empty in some cases but of the five columns atleaset one column should have some value....All the five columns should not be empty.
How do i check this scenario...
eg:Input column
ID1 ID2 ID3 ID4 ID5
12 NULL NULL 67 78
I should check to see atleast one column should have some value
How can i achieve this???
Please let me know
View 6 Replies
View Related
Jan 7, 2007
Can you tell me if this is possible? (and how to do it!!)
The application is VS2005, with sql database.
I want to check if a specific column exists in a specific table in the database and if not then add it, all via my application.
I'm happy knowing how to connect to the database & pass sql commands (as I'm doing that anyway to set off backups), but not the actual queries I'd need.
View 3 Replies
View Related
Feb 5, 2007
Well, actually, as in the title. I have a table. The script should add a column if that column doesn't exist already. I use VB to combine the two queries. So what I want:
Query1: Does the column exists:
if No,
query2: create the column
How can I achieve this?
View 6 Replies
View Related
Jul 28, 2015
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A
B
C
D
E
F
G
H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
View 4 Replies
View Related
Aug 31, 2000
I receive the following error when creating a CHECK constraint that references another column. According to the good old Wrox SQL Server book, I'm using the correct syntax. Anyone have any ideas???
Thanks in advance!
Server: Msg 8141, Level 16, State 1, Line 1
Column CHECK constraint for column 'end_date' references another column, table 'Session'.
Here's an example of the script that I'm using:
CREATE TABLE Session (
session_key char(18) NOT NULL,
course_key char(18) NOT NULL,
site_key char(18) NOT NULL,
instructor_key char(18) NOT NULL,
start_date smalldatetime NULL,
end_date smalldatetime NULL
CHECK (end_date >= start_date)
)
.
View 1 Replies
View Related
Aug 18, 1999
How do I alter a column check constraint?
I have the table:
CREATE TABLE Mytable(
mykey integer,
mycol integer
CHECK(mycol BETWEEN 1 AND 2)
PRIMARY KEY(mykey))
How do I change the constraint to
CHECK(mycol BETWEEN 0 AND 2)
...without losing any data?
Thanks!
Jim
View 1 Replies
View Related
Jun 6, 2007
Can anyone please tell me how can i create a uniqueness contraint on part of column and index that part too. i.e.
consider the following table.
table A
Col1
furadfaf
fsradfasd
dddafadsf
hjfhdfjakdj
now i want only left three characters of the Col1 to be unique and indexed.
any idea ??????
View 5 Replies
View Related
Jan 4, 2005
say i have a table, and in it are two columns, column1 and column2 and i do the following query:
SELECT column1, column2 FROM table WHERE column2 = '12345'
i want to check if column1 has all the same values, so in the first case, no
column1 column2
------- --------
4 12345
9 12345
5 12345
column1 column2
------- --------
9 12345
9 12345
9 12345
in the 2nd case, column1 contains all the same values, so yes
is there anyway i can check this? i would be doing this in a trigger.. say when a new row is inserted, the value of column1 is inserted, but col 2 is null.. so when they try to fill in the value for col2 of that row, the trigger checks to see if the value they put for col 2 is already in the table.. if it isn't, then everything is ok. but if it is already in teh table, then it checks col1 to see if all the values of col1 are the same
i hope this makes sense
thanks
View 2 Replies
View Related
May 4, 2012
i have Table A - Column A,Column B...Column B can not be a null value if Coulmn A = "xxx", how can i do this.
View 2 Replies
View Related
Mar 15, 2007
I would like to know how can i use derived column to covert the scrip that use to transformation column in DTS 2000(i've migrate my dts to ssis) i didn't know what function can check the numeric in ssis.
My scrip in transformation in dts 2000 as follow.
Function Main()
if isnumeric(DTSSource("Col012")) then
DTSDestination("SH") = int( DTSSource("Col012"))
else
DTSDestination("SH") = DTSSource("Col012")
end if
Main = DTSTransformStat_OK
End Function
Any suggestion please let's me know.
Thanks.
Best Regards,
Kus
View 5 Replies
View Related
Dec 28, 2007
Hi All,
We have a very convoluted ETL system which is pulling unnecessary data. First thought is to restrict everything so that only the columns/tables that are necessary are brought back. We have a tons of reporting stored procedures that depend on ETL tables, is there anyway we can find out which column/tables these stored procedures are using?
Thanks!
View 3 Replies
View Related
Jun 23, 2007
Dear All,
I wanted to know how do I know or check that whether a column exists in a table. What function or method should I use to find out that a column has already exists in a table.
When I run a T-SQL script which i have written does not work. Here is how I have written:
IF Object_ID('ColumnA') IS NOT NULL
ALTER TABLE [dbo].[Table1] DROP COLUMN [ColumnA]
GO
I badly need some help.
View 9 Replies
View Related
Jun 22, 2007
How to check to make sure a column does not exist before adding it? Be nice to do this in t-sql code instead of using ado.net. It seems as if the IF NOT EXISTS is not supported in SqlCe 3.1? I am trying to do this but I get the token error:
IF NOT EXISTS (
select *
from Information_SCHEMA.columns
where Table_name='authors' and column_name='NewColumn'
)
select 'no'
Is there a list of all CE supported t-sql commands?
Thanks,
Dan
View 5 Replies
View Related
Dec 19, 2007
When generating a check constraint to guarantee that a character column cannot be blank is it best to use comparison operators such as col1 <> '' or to use LEN(col1) > 0? Note that the column in marked as not nullable.
View 5 Replies
View Related
Feb 18, 2008
I have a table column can save english(single byte) or chinese(double byte) char, how to distinguish the records containing chinese(double byte) char thru sql command
thx.
View 1 Replies
View Related
May 16, 2014
Table Temporary (Staging) - 400.000 rows and 200 columns.
When i done insert from temporary table to target, i have this:
Msg 8152, Level 16, State 13, Line 9
String or binary data would be truncated.
The statement has been terminated.
How can i check which row/column truncated?
View 9 Replies
View Related
Jun 13, 2008
I am trying
IF OBJECT_ID(''tempdb..#tempTable.Column'') IS NOT NULL
But its returning me a null every time. Is there any other way to check if column exists in temporary table.
View 4 Replies
View Related
Mar 28, 2014
select '$ '+ CONVERT(varchar,CONVERT(decimal(10,0),CONVERT(money, Amt_Value)),1) as [Amount]
from Products
How can I sum this column values and need to set a validation like the column has null values it has to return zero.
View 2 Replies
View Related
Apr 25, 2015
I have a SQL Server 2005 DB in which a design change requires me to drop a column.Is there a way of identifying all references in the DB including functions & stored procs's that reference this column? I understand there are 3rd party products that can do this but was wondering if there was something baked into SQL Server itself.
View 8 Replies
View Related
Feb 14, 2000
Hello all,
I am trying to add columns to several tables in a singular database. I want to check to see if the column exists in a given table first before executing the alter table command so that I can skip that command if the column already exists, and avoid aborting my script prematurely. I can't seem to find a way to check for the column's existance. Any suggestions?
View 2 Replies
View Related
Apr 21, 2014
I have a table called SrcReg which is having a column name called IsSortSeqNo smallint. I am mapping this column in SSIS and the problem comes when I try to execute against different database which has this table but the column name as ISSortSeqNo. I mean both databases having same name but one with upper case. So SSIS fails executing due to meta validation issue.Is there any way to check whether the column name is in small case or upper case through query?
View 8 Replies
View Related
Sep 9, 2015
I have huge export files in a DB and i need to check if there are any datasets that have the same value in the first column, but a different in another one, via a query of course.
Like this:
ID IS NULL
1 1
2 1
3 0
1 0
The expected ID i get as a result of my query should be 1 in this case.
View 6 Replies
View Related
Apr 9, 2008
nedd help from the from the wonderful people here
i am stuck in !
i need to check each column succession
the chacking is for 3 columns (day1+day11+day111) (day2+day22+day222) (day3+day33+day333) (day4+day44+day444) (day5+day55+day555)
and if i don't have the value
than add to a new result row
if not show the missing value in the result row at END
if i have in the fields columns A B C than
ok-A
ok-B
ok-C
if i don't have the value A B C than than show it like this
no-A
no-B
no-Cif i don't have the value c than than show it like this no-C (on the left )
if i don't have the value a than than show it like this no-A (on middle)
if i don't have the value b than than show it like this no-B (on right)
IF have duplicity a or b or c show in the result row at END 2-C=(thre is tow time the C)
ignore the character '*' and '-' and Q and Y
id
fname
val
day1
day11
day111
day2
day22
day222
day3
day33
day333
day4
day44
day444
day5
day55
day555
111
aaaa
2
111
aaaa
1
A
NULL
NULL
B
C
NULL
NULL
-
C
-
-
-
C
222
bbbb
2
222
bbbb
1
B
NULL
NULL
C
B
NULL
NULL
B
C
NULL
NULL
333
cccc
2
333
cccc
1
C
NULL
NULL
*
444
dddd
2
444
dddd
1
*
A
B
NULL
NULL
555
EEE
2
-
-
-
-
Q
555
EEE
1
*
*
*
Q
0
ok-A
ok-B
ok-C
ok-A
ok-B
ok-C
ok-A
ok-B
NO-C
NO-A
ok-B
ok-C
NO-A
NO-B
2-C
i have similar code i send it
TNX for the help
View 23 Replies
View Related
