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

Check For Column Before Adding A Column

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

SUBSTRING In User Defined Function - Invalid Column

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

Can A Column Be Derived Using Substring But The Parameters Are A Result Of A Select Statement?

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

A Table/column To Table/column Data Check (was Help Please, SQL Something Simple)

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

How To Check If A Column Is Numeric Or Not

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

How To Check For A Numeric Value In A Column

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

Varchar Column Check

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

Cannot Check Column In Where Clause

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

Multiple Column Check

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

Check To See Atleast One Column Has Value

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

Check For Column && Insert

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

How To: Check If A Column Exists, And If Not, Add It?

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

Integration Services :: Add New Column Based On Existing Column Using Derived Column Task?

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

CHECK Constraint - Referencing Another Column

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

ALTERing A Column CHECK Constraint

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

Check Constraint On Part Of Column

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

Any Way To Check If All The Values In A Column Returned Are The Same Value?

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

Check Constrain Based On Another Column

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

How To Check Numeric In Derived Column?

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

Table/column Dependency Check

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

How To Check The Existence Of A Column In A Table

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

How To Check That Column Does Not Exists Before Adding It?

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

Check Constraint On Character Column

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

How To Check Non-english Char Value In A Table Column

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

SQL Server 2012 :: How To Check Which Row / Column Truncated

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

How To Check If Column Exists In Temporary Table

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

Sum Column Values And Also Check Null Condition

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

Transact SQL :: Check DB Consistency After DROP COLUMN

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

Check For Column Existance Before Alter Table Statement

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

SQL Server 2012 :: How To Check Column Name In Case Sensitive

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

SQL Server 2014 :: Check If Two Rows Have A Different Value In A Specific Column

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

Please Help Unpivoted Pivot Check Each Column Succession-and Add Result Row At END

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







Copyrights 2005-15 www.BigResource.com, All rights reserved