How to add check constraint for flag column when we don't know exact format in sql?
up vote
1
down vote
favorite
1
Here first letter should be an alpha, column may contain any number of data separated by " / " ,for every stating letter I want alphabet, after first letter it may contain 3 or 4 letter. How to do this dynamically.
I am thinking of... first i can find position of " / " add it to one, here we will get alphabet position then I can check whether it is an alpha or numerical character
sql
sql-server tsql edited Nov 7 at 12:52
Larnu
13.5k31430
asked Nov 7 at 12:41
sachi
154
add a comment |
up vote
1
down vote
favorite
1
Here first letter should be an alpha, column may contain any number of data separated by " / " ,for every stating letter I want alphabet, after first letter it may contain 3 or 4 letter. How to do this dynamically.
I am thinking of... first i can find position of " / " add it to one, here we will get alphabet position then I can check whether it is an alpha or numerical character
sql
sql-server tsql edited Nov 7 at 12:52
Larnu
13.5k31430
asked Nov 7 at 12:41
sachi
154
4
For some reason, this looks like a sequence of codes to me. If so, you should not be storing them in a string. You should have a separate row for each code.
– Gordon Linoff
Nov 7 at 12:45
2
You can do this in T-SQL, but not without a function, and not efficiently. Gordon is right on the money: if it's possible to store these "flags" in a properly normalized way, you should do so. A check for each individual component is much simpler, as it avoids the hairy problem of string splitting in T-SQL.
– Jeroen Mostert
Nov 7 at 12:49
1
You could do this with a check constraint, however, you're going to need to create a scalar function to do so, which could ruin performance of yourINSERT/UPDATEstatements. Ideally, as @GordonLinoff recommended, you need to normalise your data. I would also recommend tagging the version of SQL Server you are using.
– Larnu
Nov 7 at 12:50
@JeroenMostert you just beat me to it.
– Larnu
Nov 7 at 12:50
4
And as a bonus: Read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!
– Zohar Peled
Nov 7 at 12:54
add a comment |
up vote
1
down vote
favorite
1
up vote
1
down vote
favorite
1
1
Here first letter should be an alpha, column may contain any number of data separated by " / " ,for every stating letter I want alphabet, after first letter it may contain 3 or 4 letter. How to do this dynamically.
I am thinking of... first i can find position of " / " add it to one, here we will get alphabet position then I can check whether it is an alpha or numerical character
sql
sql-server tsql edited Nov 7 at 12:52
Larnu
13.5k31430
asked Nov 7 at 12:41
sachi
154
Here first letter should be an alpha, column may contain any number of data separated by " / " ,for every stating letter I want alphabet, after first letter it may contain 3 or 4 letter. How to do this dynamically.
I am thinking of... first i can find position of " / " add it to one, here we will get alphabet position then I can check whether it is an alpha or numerical character
sql
sql-server tsql sql
sql-server tsql edited Nov 7 at 12:52
Larnu
13.5k31430
asked Nov 7 at 12:41
sachi
154
edited Nov 7 at 12:52
Larnu
13.5k31430
asked Nov 7 at 12:41
sachi
154
edited Nov 7 at 12:52
Larnu
13.5k31430
edited Nov 7 at 12:52
Larnu
13.5k31430
edited Nov 7 at 12:52
Larnu
13.5k31430
13.5k31430
asked Nov 7 at 12:41
sachi
154
asked Nov 7 at 12:41
sachi
154
asked Nov 7 at 12:41
sachi
154
154
4
For some reason, this looks like a sequence of codes to me. If so, you should not be storing them in a string. You should have a separate row for each code.
– Gordon Linoff
Nov 7 at 12:45
2
You can do this in T-SQL, but not without a function, and not efficiently. Gordon is right on the money: if it's possible to store these "flags" in a properly normalized way, you should do so. A check for each individual component is much simpler, as it avoids the hairy problem of string splitting in T-SQL.
– Jeroen Mostert
Nov 7 at 12:49
1
You could do this with a check constraint, however, you're going to need to create a scalar function to do so, which could ruin performance of yourINSERT/UPDATEstatements. Ideally, as @GordonLinoff recommended, you need to normalise your data. I would also recommend tagging the version of SQL Server you are using.
– Larnu
Nov 7 at 12:50
@JeroenMostert you just beat me to it.
– Larnu
Nov 7 at 12:50
4
And as a bonus: Read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!
– Zohar Peled
Nov 7 at 12:54
add a comment |
4
For some reason, this looks like a sequence of codes to me. If so, you should not be storing them in a string. You should have a separate row for each code.
– Gordon Linoff
Nov 7 at 12:45
2
You can do this in T-SQL, but not without a function, and not efficiently. Gordon is right on the money: if it's possible to store these "flags" in a properly normalized way, you should do so. A check for each individual component is much simpler, as it avoids the hairy problem of string splitting in T-SQL.
– Jeroen Mostert
Nov 7 at 12:49
1
You could do this with a check constraint, however, you're going to need to create a scalar function to do so, which could ruin performance of yourINSERT/UPDATEstatements. Ideally, as @GordonLinoff recommended, you need to normalise your data. I would also recommend tagging the version of SQL Server you are using.
– Larnu
Nov 7 at 12:50
@JeroenMostert you just beat me to it.
– Larnu
Nov 7 at 12:50
4
And as a bonus: Read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!
– Zohar Peled
Nov 7 at 12:54
4
4
For some reason, this looks like a sequence of codes to me. If so, you should not be storing them in a string. You should have a separate row for each code.
– Gordon Linoff
Nov 7 at 12:45
For some reason, this looks like a sequence of codes to me. If so, you should not be storing them in a string. You should have a separate row for each code.
– Gordon Linoff
Nov 7 at 12:45
2
2
You can do this in T-SQL, but not without a function, and not efficiently. Gordon is right on the money: if it's possible to store these "flags" in a properly normalized way, you should do so. A check for each individual component is much simpler, as it avoids the hairy problem of string splitting in T-SQL.
– Jeroen Mostert
Nov 7 at 12:49
You can do this in T-SQL, but not without a function, and not efficiently. Gordon is right on the money: if it's possible to store these "flags" in a properly normalized way, you should do so. A check for each individual component is much simpler, as it avoids the hairy problem of string splitting in T-SQL.
– Jeroen Mostert
Nov 7 at 12:49
1
1
You could do this with a check constraint, however, you're going to need to create a scalar function to do so, which could ruin performance of your
INSERT/UPDATE statements. Ideally, as @GordonLinoff recommended, you need to normalise your data. I would also recommend tagging the version of SQL Server you are using.– Larnu
Nov 7 at 12:50
You could do this with a check constraint, however, you're going to need to create a scalar function to do so, which could ruin performance of your
INSERT/UPDATE statements. Ideally, as @GordonLinoff recommended, you need to normalise your data. I would also recommend tagging the version of SQL Server you are using.– Larnu
Nov 7 at 12:50
@JeroenMostert you just beat me to it.
– Larnu
Nov 7 at 12:50
@JeroenMostert you just beat me to it.
– Larnu
Nov 7 at 12:50
4
4
And as a bonus: Read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!
– Zohar Peled
Nov 7 at 12:54
And as a bonus: Read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!
– Zohar Peled
Nov 7 at 12:54
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
You should take on board the comments about normalisation - however you should be able to do this without a UDF.
CREATE TABLE #T
(
Flag VARCHAR(100) CHECK (
/*Only contains alpha, digits and slash*/
Flag NOT LIKE '%[^a-zA-Z0-9/]%' COLLATE Latin1_General_100_BIN2 AND
/*Doesn't end in a slash*/
Flag NOT LIKE '%/' AND
/*Start of the string and every slash should be followed by an alpha*/
'/' + Flag NOT LIKE '%/[^a-zA-Z]%' COLLATE Latin1_General_100_BIN2 AND
/*Should never have a digit immediately followed by an alpha*/
Flag NOT LIKE '%[0-9][a-zA-Z]%' COLLATE Latin1_General_100_BIN2 AND
/*Should never have two alpha in a row*/
Flag NOT LIKE '%[a-zA-Z][a-zA-Z]%' COLLATE Latin1_General_100_BIN2 AND
/*Should never have 5 digits in a row*/
Flag NOT LIKE '%[0-9][0-9][0-9][0-9][0-9]%' COLLATE Latin1_General_100_BIN2 AND
/*Should not have codes of less than 4 characters*/
'/' + Flag + '/' NOT LIKE '%/_/%' AND
'/' + Flag + '/' NOT LIKE '%/__/%' AND
'/' + Flag + '/' NOT LIKE '%/___/%'
)
);
answered Nov 7 at 13:36
Martin Smith
338k58566673
1
I'd upvote this, but technically correct or not, this is the sort of "too clever by half" thing I hope to never see in a database. It's just too hard to maintain if the rules ever change, and too hard to check for edge conditions. (Point in case: the current version deems'A000/'to be correct, which is not consistent with deeming'/A000'to be incorrect.)
– Jeroen Mostert
Nov 7 at 14:02
1
@JeroenMostert - yep would need a lot of tSQLt tests. And of course the OP likely shouldn't be storing it in the first place in this format
– Martin Smith
Nov 7 at 15:58
1
@JeroenMostert I agree that this type of solution can be kludgy but is not bad provided that rules don't change too often. I appreciate Martin demonstrating an alternative to using a T-SQL scalar UDF which would be a profoundly worse solution. I would probably have a condition forNOT LIKE '[^a-zA-Z]%'which addresses your /A000 example and a number of edge cases.
– Alan Burstein
Nov 7 at 22:00
1
@AlanBurstein - though the scalar UDF solution could be a good one soon. blogs.msdn.microsoft.com/sqlserverstorageengine/2018/11/07/…
– Martin Smith
Nov 8 at 9:29
@MartinSmith - thanks for posting that - great read and great news!
– Alan Burstein
Nov 8 at 15:12
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
You should take on board the comments about normalisation - however you should be able to do this without a UDF.
CREATE TABLE #T
(
Flag VARCHAR(100) CHECK (
/*Only contains alpha, digits and slash*/
Flag NOT LIKE '%[^a-zA-Z0-9/]%' COLLATE Latin1_General_100_BIN2 AND
/*Doesn't end in a slash*/
Flag NOT LIKE '%/' AND
/*Start of the string and every slash should be followed by an alpha*/
'/' + Flag NOT LIKE '%/[^a-zA-Z]%' COLLATE Latin1_General_100_BIN2 AND
/*Should never have a digit immediately followed by an alpha*/
Flag NOT LIKE '%[0-9][a-zA-Z]%' COLLATE Latin1_General_100_BIN2 AND
/*Should never have two alpha in a row*/
Flag NOT LIKE '%[a-zA-Z][a-zA-Z]%' COLLATE Latin1_General_100_BIN2 AND
/*Should never have 5 digits in a row*/
Flag NOT LIKE '%[0-9][0-9][0-9][0-9][0-9]%' COLLATE Latin1_General_100_BIN2 AND
/*Should not have codes of less than 4 characters*/
'/' + Flag + '/' NOT LIKE '%/_/%' AND
'/' + Flag + '/' NOT LIKE '%/__/%' AND
'/' + Flag + '/' NOT LIKE '%/___/%'
)
);
answered Nov 7 at 13:36
Martin Smith
338k58566673
1
I'd upvote this, but technically correct or not, this is the sort of "too clever by half" thing I hope to never see in a database. It's just too hard to maintain if the rules ever change, and too hard to check for edge conditions. (Point in case: the current version deems'A000/'to be correct, which is not consistent with deeming'/A000'to be incorrect.)
– Jeroen Mostert
Nov 7 at 14:02
1
@JeroenMostert - yep would need a lot of tSQLt tests. And of course the OP likely shouldn't be storing it in the first place in this format
– Martin Smith
Nov 7 at 15:58
1
@JeroenMostert I agree that this type of solution can be kludgy but is not bad provided that rules don't change too often. I appreciate Martin demonstrating an alternative to using a T-SQL scalar UDF which would be a profoundly worse solution. I would probably have a condition forNOT LIKE '[^a-zA-Z]%'which addresses your /A000 example and a number of edge cases.
– Alan Burstein
Nov 7 at 22:00
1
@AlanBurstein - though the scalar UDF solution could be a good one soon. blogs.msdn.microsoft.com/sqlserverstorageengine/2018/11/07/…
– Martin Smith
Nov 8 at 9:29
@MartinSmith - thanks for posting that - great read and great news!
– Alan Burstein
Nov 8 at 15:12
add a comment |
up vote
0
down vote
accepted
You should take on board the comments about normalisation - however you should be able to do this without a UDF.
CREATE TABLE #T
(
Flag VARCHAR(100) CHECK (
/*Only contains alpha, digits and slash*/
Flag NOT LIKE '%[^a-zA-Z0-9/]%' COLLATE Latin1_General_100_BIN2 AND
/*Doesn't end in a slash*/
Flag NOT LIKE '%/' AND
/*Start of the string and every slash should be followed by an alpha*/
'/' + Flag NOT LIKE '%/[^a-zA-Z]%' COLLATE Latin1_General_100_BIN2 AND
/*Should never have a digit immediately followed by an alpha*/
Flag NOT LIKE '%[0-9][a-zA-Z]%' COLLATE Latin1_General_100_BIN2 AND
/*Should never have two alpha in a row*/
Flag NOT LIKE '%[a-zA-Z][a-zA-Z]%' COLLATE Latin1_General_100_BIN2 AND
/*Should never have 5 digits in a row*/
Flag NOT LIKE '%[0-9][0-9][0-9][0-9][0-9]%' COLLATE Latin1_General_100_BIN2 AND
/*Should not have codes of less than 4 characters*/
'/' + Flag + '/' NOT LIKE '%/_/%' AND
'/' + Flag + '/' NOT LIKE '%/__/%' AND
'/' + Flag + '/' NOT LIKE '%/___/%'
)
);
answered Nov 7 at 13:36
Martin Smith
338k58566673
1
I'd upvote this, but technically correct or not, this is the sort of "too clever by half" thing I hope to never see in a database. It's just too hard to maintain if the rules ever change, and too hard to check for edge conditions. (Point in case: the current version deems'A000/'to be correct, which is not consistent with deeming'/A000'to be incorrect.)
– Jeroen Mostert
Nov 7 at 14:02
1
@JeroenMostert - yep would need a lot of tSQLt tests. And of course the OP likely shouldn't be storing it in the first place in this format
– Martin Smith
Nov 7 at 15:58
1
@JeroenMostert I agree that this type of solution can be kludgy but is not bad provided that rules don't change too often. I appreciate Martin demonstrating an alternative to using a T-SQL scalar UDF which would be a profoundly worse solution. I would probably have a condition forNOT LIKE '[^a-zA-Z]%'which addresses your /A000 example and a number of edge cases.
– Alan Burstein
Nov 7 at 22:00
1
@AlanBurstein - though the scalar UDF solution could be a good one soon. blogs.msdn.microsoft.com/sqlserverstorageengine/2018/11/07/…
– Martin Smith
Nov 8 at 9:29
@MartinSmith - thanks for posting that - great read and great news!
– Alan Burstein
Nov 8 at 15:12
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
You should take on board the comments about normalisation - however you should be able to do this without a UDF.
CREATE TABLE #T
(
Flag VARCHAR(100) CHECK (
/*Only contains alpha, digits and slash*/
Flag NOT LIKE '%[^a-zA-Z0-9/]%' COLLATE Latin1_General_100_BIN2 AND
/*Doesn't end in a slash*/
Flag NOT LIKE '%/' AND
/*Start of the string and every slash should be followed by an alpha*/
'/' + Flag NOT LIKE '%/[^a-zA-Z]%' COLLATE Latin1_General_100_BIN2 AND
/*Should never have a digit immediately followed by an alpha*/
Flag NOT LIKE '%[0-9][a-zA-Z]%' COLLATE Latin1_General_100_BIN2 AND
/*Should never have two alpha in a row*/
Flag NOT LIKE '%[a-zA-Z][a-zA-Z]%' COLLATE Latin1_General_100_BIN2 AND
/*Should never have 5 digits in a row*/
Flag NOT LIKE '%[0-9][0-9][0-9][0-9][0-9]%' COLLATE Latin1_General_100_BIN2 AND
/*Should not have codes of less than 4 characters*/
'/' + Flag + '/' NOT LIKE '%/_/%' AND
'/' + Flag + '/' NOT LIKE '%/__/%' AND
'/' + Flag + '/' NOT LIKE '%/___/%'
)
);
answered Nov 7 at 13:36
Martin Smith
338k58566673
You should take on board the comments about normalisation - however you should be able to do this without a UDF.
CREATE TABLE #T
(
Flag VARCHAR(100) CHECK (
/*Only contains alpha, digits and slash*/
Flag NOT LIKE '%[^a-zA-Z0-9/]%' COLLATE Latin1_General_100_BIN2 AND
/*Doesn't end in a slash*/
Flag NOT LIKE '%/' AND
/*Start of the string and every slash should be followed by an alpha*/
'/' + Flag NOT LIKE '%/[^a-zA-Z]%' COLLATE Latin1_General_100_BIN2 AND
/*Should never have a digit immediately followed by an alpha*/
Flag NOT LIKE '%[0-9][a-zA-Z]%' COLLATE Latin1_General_100_BIN2 AND
/*Should never have two alpha in a row*/
Flag NOT LIKE '%[a-zA-Z][a-zA-Z]%' COLLATE Latin1_General_100_BIN2 AND
/*Should never have 5 digits in a row*/
Flag NOT LIKE '%[0-9][0-9][0-9][0-9][0-9]%' COLLATE Latin1_General_100_BIN2 AND
/*Should not have codes of less than 4 characters*/
'/' + Flag + '/' NOT LIKE '%/_/%' AND
'/' + Flag + '/' NOT LIKE '%/__/%' AND
'/' + Flag + '/' NOT LIKE '%/___/%'
)
);
answered Nov 7 at 13:36
Martin Smith
338k58566673
edited Nov 7 at 22:04
answered Nov 7 at 13:36
Martin Smith
338k58566673
answered Nov 7 at 13:36
Martin Smith
338k58566673
answered Nov 7 at 13:36
Martin Smith
338k58566673
338k58566673
1
I'd upvote this, but technically correct or not, this is the sort of "too clever by half" thing I hope to never see in a database. It's just too hard to maintain if the rules ever change, and too hard to check for edge conditions. (Point in case: the current version deems'A000/'to be correct, which is not consistent with deeming'/A000'to be incorrect.)
– Jeroen Mostert
Nov 7 at 14:02
1
@JeroenMostert - yep would need a lot of tSQLt tests. And of course the OP likely shouldn't be storing it in the first place in this format
– Martin Smith
Nov 7 at 15:58
1
@JeroenMostert I agree that this type of solution can be kludgy but is not bad provided that rules don't change too often. I appreciate Martin demonstrating an alternative to using a T-SQL scalar UDF which would be a profoundly worse solution. I would probably have a condition forNOT LIKE '[^a-zA-Z]%'which addresses your /A000 example and a number of edge cases.
– Alan Burstein
Nov 7 at 22:00
1
@AlanBurstein - though the scalar UDF solution could be a good one soon. blogs.msdn.microsoft.com/sqlserverstorageengine/2018/11/07/…
– Martin Smith
Nov 8 at 9:29
@MartinSmith - thanks for posting that - great read and great news!
– Alan Burstein
Nov 8 at 15:12
add a comment |
1
I'd upvote this, but technically correct or not, this is the sort of "too clever by half" thing I hope to never see in a database. It's just too hard to maintain if the rules ever change, and too hard to check for edge conditions. (Point in case: the current version deems'A000/'to be correct, which is not consistent with deeming'/A000'to be incorrect.)
– Jeroen Mostert
Nov 7 at 14:02
1
@JeroenMostert - yep would need a lot of tSQLt tests. And of course the OP likely shouldn't be storing it in the first place in this format
– Martin Smith
Nov 7 at 15:58
1
@JeroenMostert I agree that this type of solution can be kludgy but is not bad provided that rules don't change too often. I appreciate Martin demonstrating an alternative to using a T-SQL scalar UDF which would be a profoundly worse solution. I would probably have a condition forNOT LIKE '[^a-zA-Z]%'which addresses your /A000 example and a number of edge cases.
– Alan Burstein
Nov 7 at 22:00
1
@AlanBurstein - though the scalar UDF solution could be a good one soon. blogs.msdn.microsoft.com/sqlserverstorageengine/2018/11/07/…
– Martin Smith
Nov 8 at 9:29
@MartinSmith - thanks for posting that - great read and great news!
– Alan Burstein
Nov 8 at 15:12
1
1
I'd upvote this, but technically correct or not, this is the sort of "too clever by half" thing I hope to never see in a database. It's just too hard to maintain if the rules ever change, and too hard to check for edge conditions. (Point in case: the current version deems
'A000/' to be correct, which is not consistent with deeming '/A000' to be incorrect.)– Jeroen Mostert
Nov 7 at 14:02
I'd upvote this, but technically correct or not, this is the sort of "too clever by half" thing I hope to never see in a database. It's just too hard to maintain if the rules ever change, and too hard to check for edge conditions. (Point in case: the current version deems
'A000/' to be correct, which is not consistent with deeming '/A000' to be incorrect.)– Jeroen Mostert
Nov 7 at 14:02
1
1
@JeroenMostert - yep would need a lot of tSQLt tests. And of course the OP likely shouldn't be storing it in the first place in this format
– Martin Smith
Nov 7 at 15:58
@JeroenMostert - yep would need a lot of tSQLt tests. And of course the OP likely shouldn't be storing it in the first place in this format
– Martin Smith
Nov 7 at 15:58
1
1
@JeroenMostert I agree that this type of solution can be kludgy but is not bad provided that rules don't change too often. I appreciate Martin demonstrating an alternative to using a T-SQL scalar UDF which would be a profoundly worse solution. I would probably have a condition for
NOT LIKE '[^a-zA-Z]%' which addresses your /A000 example and a number of edge cases.– Alan Burstein
Nov 7 at 22:00
@JeroenMostert I agree that this type of solution can be kludgy but is not bad provided that rules don't change too often. I appreciate Martin demonstrating an alternative to using a T-SQL scalar UDF which would be a profoundly worse solution. I would probably have a condition for
NOT LIKE '[^a-zA-Z]%' which addresses your /A000 example and a number of edge cases.– Alan Burstein
Nov 7 at 22:00
1
1
@AlanBurstein - though the scalar UDF solution could be a good one soon. blogs.msdn.microsoft.com/sqlserverstorageengine/2018/11/07/…
– Martin Smith
Nov 8 at 9:29
@AlanBurstein - though the scalar UDF solution could be a good one soon. blogs.msdn.microsoft.com/sqlserverstorageengine/2018/11/07/…
– Martin Smith
Nov 8 at 9:29
@MartinSmith - thanks for posting that - great read and great news!
– Alan Burstein
Nov 8 at 15:12
@MartinSmith - thanks for posting that - great read and great news!
– Alan Burstein
Nov 8 at 15:12
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53189680%2fhow-to-add-check-constraint-for-flag-column-when-we-dont-know-exact-format-in-s%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
4
For some reason, this looks like a sequence of codes to me. If so, you should not be storing them in a string. You should have a separate row for each code.
– Gordon Linoff
Nov 7 at 12:45
2
You can do this in T-SQL, but not without a function, and not efficiently. Gordon is right on the money: if it's possible to store these "flags" in a properly normalized way, you should do so. A check for each individual component is much simpler, as it avoids the hairy problem of string splitting in T-SQL.
– Jeroen Mostert
Nov 7 at 12:49
1
You could do this with a check constraint, however, you're going to need to create a scalar function to do so, which could ruin performance of your
INSERT/UPDATEstatements. Ideally, as @GordonLinoff recommended, you need to normalise your data. I would also recommend tagging the version of SQL Server you are using.– Larnu
Nov 7 at 12:50
@JeroenMostert you just beat me to it.
– Larnu
Nov 7 at 12:50
4
And as a bonus: Read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!
– Zohar Peled
Nov 7 at 12:54