String Manipulation
-
I am trying to construct a query where I am concatinating 3 numeric columns on the right side of a WHERE. I have found many examples for the right side of SELECT but none for WHERE. Everything I try either returns the wrong results or syntax errors.
Any help would be appreciated. Thanks in advance.
-
you'll want to use the convert function most likely...
assume a db has three int or other numeric columns:
SELECT * from sometable
WHERE CONVERT(VARCHAR,MAJORVERSION) + '.' + CONVERT(VARCHAR,MINORVERSION) + '.' + CONVERT(VARCHAR,REVISIONNUMBER) = '7.1.5'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I tried:
where
convert(varchar,BATCH_DATE)
+ '.' + convert(varchar,batch_nmbr)
+ '.' + convert(varchar,BATCH_CTR) =
'20050404.09000.0001'
I got an sqlcode of 100
When I changed the "=" to ">=" it returned that particular row???
-
I've seen and tried solutions like this, and they are nothing but problems.
You will be better off parsing the value for your where clause.
Where Batch_Date >= '20050404'
and Batch_nbr >= '09000'
and Batch_ctr >= '0001'
Or created a calculated column on the table concatenating these values.
But once you cast these values into a string >= does not work the same.
Because in a string
'100' is Before '20'
for example
declare @table table (pk int identity, value varchar(100))
insert into @table (value)
select '1' union
select '2' union
select '10' union
select '15' union
select '20' union
select '100'
select *
from @table
order by value
Results
1 1
2 10
3 100
4 15
5 2
6 20
-
lookjs like you are assuming a specific date format and preceeding zeros that you did not mention previously:
well this will work, but I agree with Ray that this isn't the best way to check for data:
DECLARE @SAMPLE TABLE(BATCH_DATE datetime,batch_nmbr int,BATCH_CTR int)
insert into @SAMPLE(BATCH_DATE,batch_nmbr,BATCH_CTR) VALUES ('4/4/2005',9000,1)
insert into @SAMPLE(BATCH_DATE,batch_nmbr,BATCH_CTR) VALUES ('4/4/2005',9000,2)
insert into @SAMPLE(BATCH_DATE,batch_nmbr,BATCH_CTR) VALUES ('4/5/2005',9001,1)
insert into @SAMPLE(BATCH_DATE,batch_nmbr,BATCH_CTR) VALUES ('4/6/2005',9000,2)
insert into @SAMPLE(BATCH_DATE,batch_nmbr,BATCH_CTR) VALUES ('4/6/2005',9001,1)
SELECT
CONVERT(VARCHAR,BATCH_DATE,112) ,
RIGHT('00000' + convert(varchar,batch_nmbr),5),
RIGHT('00000' + convert(varchar,BATCH_CTR),5),
CONVERT(VARCHAR,BATCH_DATE,112) + '.' + RIGHT('00000' + convert(varchar,batch_nmbr),5)+ '.' + RIGHT('00000' + convert(varchar,BATCH_CTR),5) AS NM
FROM @SAMPLE
20050404 09000 00001 20050404.09000.00001 20050404 09000 00002 20050404.09000.00002 20050405 09001 00001 20050405.09001.00001 20050406 09000 00002 20050406.09000.00002 20050406 09001 00001 20050406.09001.00001 SELECT
CONVERT(VARCHAR,BATCH_DATE,112) ,
RIGHT('00000' + convert(varchar,batch_nmbr),5),
RIGHT('00000' + convert(varchar,BATCH_CTR),5),
CONVERT(VARCHAR,BATCH_DATE,112) + '.' + RIGHT('00000' + convert(varchar,batch_nmbr),5)+ '.' + RIGHT('00000' + convert(varchar,BATCH_CTR),5) AS NM
FROM @SAMPLE
WHERE CONVERT(VARCHAR,BATCH_DATE,112) + '.' + RIGHT('00000' + convert(varchar,batch_nmbr),5)+ '.' + RIGHT('00000' + convert(varchar,BATCH_CTR),5) >='20050404.09001.00001'
20050405 09001 00001 20050405.09001.00001 20050406 09000 00002 20050406.09000.00002 20050406 09001 00001 20050406.09001.00001 Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
All the fields are interger numbers.
I would have never figured out this solution.
You people are great.
-
Try this:
SELECT MyColumnName_That_I_Free_Choosed
FROM (
SELECT
(Convert(varchar(Numeric),Column1)+ '.' +
Convert(varchar(Numeric),Column2)+ '.' +
Convert(varchar(Numeric),Column3)) MyColumnName_That_I_Free_Choosed
FROM YourSourceTables ) as My_PseudoTable
WHERE
MyColumnName_That_I_Free_Choosed='What I want to filter'
Have a nice day! You can insert into a temporal table, but its very expensive for the sql server or any data base engine
Gabriel
-
Also, you should ALWAYS use length parameter when converting to some string datatype (CHAR, VARCHAR...) or defining columns or variables with it.
If you skip the parameter while converting, not the most suitable length will be used, but a default, which is 30 for this case and in this version of SQL Server.
But, if you forget to specify length when declaring CHAR/VARCHAR variable, the default is 1 and everything after the first character will be cut off when you try to assign value to this variable.
CONVERT(varchar,column_name) can cause very nasty and hard to find problems, because it truncates the string automatically at 30 characters without any warning. I know that your data is always shorter, but not specifying length is bad practice and pure laziness.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply