Variables in T-SQL

问题:

I have only 1 column in my table, in this table there are inputs like 990x70, 980x50. I need the values left and right of the 'x' to calculate inches of these 2 values. With this code I take only last registered entry from database. How can I get all entries? (Note: I have to use variables in this project.)

   declare @Value1 numeric(18,1)
   declare @Value2 numeric(18,1)


   select 
   @Value2 = SUBSTRING(
       [Values], 
       CHARINDEX('x', [Values]) + 1, 
       LEN([Values])) ,

   @Value1 = SUBSTRING(
       [Values], 
       1, 
       CHARINDEX('x', [Values]) - 1)

   from myTable

   select @Value1=@Value1/(2.54)
   select @Value2=@Value2/(2.54)
   select @Value1,@Value2 from myTable

Edit: There are 4 different sizes in my table and I get the same result 4 times. I want to get all results not only 1.

回答1:

Now, admittedly, I'm not totally clear on what you're asking. But it sounds like something like this should work:

SELECT CONVERT(NUMERIC(18,1), SUBSTRING([Values], CHARINDEX('x', [Values]) + 1, LEN([Values]))) / 2.54,
       CONVERT(NUMERIC(18,1), SUBSTRING([Values], 1, CHARINDEX('x', [Values]) - 1)) / 2.54
FROM myTable

That should just do the same thing as you're doing, but without any of the variables (which are, in your usage, inherently one-dimensional).

回答2:

If you must use variables, start with this:

declare @Delimiter varchar(1) 
declare @CMtoInches DECIMAL(19,2)

Set @delimiter = 'x'
Set @CMtoInches = 2.54

and see if you can work out how to integrate it into Matthew's code.