select statement to truncate and append column values

Question

i have a table which contains columns

[id],[StartsWith],[Length] ,[MinExt] ,[MaxExt],[isDID] ,[DeleteDigits] ,[AppendDigits]

208 ,     61     ,    5    ,  61000  ,  61999 ,   0    ,       2       ,   22058

209 ,     63     ,    5    ,  63000  ,  63999 ,   0    ,       2       ,  26518

now let suppose if user sends a number say 61205 i have to check that in which row that number(i.e 61205) exist by checking between MinExt and MaxExt

after that i have to truncate x digits from 61205 mentioned in corresponding deleteDigits from the start of the number (in this case 2 digits will be truncated) after truncate i have to append 205 in last of [appenddigits] column. which will make a complete number like 22058205.

i have to do this through select statement as it will be an inner query. or if anyone can suggest something else i will be very thankful.

Are you sure the expected result is 22058205? truncate usually means remove last digits and append usually occurs at the end so a number of 61222058 seems to make more sense to me. — xQbert, Apr 11 '17 at 13:08
every time will you take last 3 digits or you truncate first 2 digits? — Rams, Apr 11 '17 at 13:08
I'll give you a trace: use SUBSTRING, use CAST, use WHERE and use + operator... — etsa, Apr 11 '17 at 13:16
@Rams length of the number could be any number in this case its 5 but length can vary. or truncated digits will be the number in deletedigit column. — waqar ahmed somra, Apr 12 '17 at 04:47
@abhi its not a homework or any assignment. i am doing a project for a company. — waqar ahmed somra, Apr 12 '17 at 04:48

score 1 · Accepted Answer · answered Apr 11 '17 at 13:18

1

Try this:

DECLARE @VAL NVARCHAR(100) = '61205'

SELECT AppendDigits + SUBSTRING(@VAL, 3, LEN(@VAL) - 2)
FROM   YOUR_TABLE
WHERE  CAST(@VAL AS int) BETWEEN MinExt AND MaxExt

answered Apr 11 '17 at 13:18

McNets

10,352
3
32
61

thanks for your reply but i need solution as a inner query – waqar ahmed somra Apr 12 '17 at 04:50
I am marking this as answer as i changed my requirement from inner query to stored procedure and then i used your query. thanks it helped. – waqar ahmed somra Apr 12 '17 at 06:15

score 0 · Answer 2 · answered Apr 11 '17 at 13:09

0

If the lookup table mentioned in your question has few rows, you could use a multi-level Case Statement to do this. Although it would look cumbersome, it will do the job perfectly without dependence on other code.

If this table is likely to have many rows, you may want to use a SQL Server User-Defined function.

answered Apr 11 '17 at 13:09

Whirl Mind

884
1
9
18

lookup table has many rows more than 4000. and for userdefined function means i have to call function for every number(if user is sending more than one number)? – waqar ahmed somra Apr 12 '17 at 04:52
Thanks for your advice i created a stored procedure and then implemented the lopic. thanks again – waqar ahmed somra Apr 12 '17 at 06:16
Ok. Just saying for future needs of similar nature, the use of a user-defined function (in-line functions) will help you to still use it in select query or an inner query. Have a good day. – Whirl Mind Apr 14 '17 at 17:03

select statement to truncate and append column values

2 Answers2