Solved

TSQL CODE HELP

Posted on 2013-06-06
11
250 Views
Last Modified: 2013-06-26
Hi,

Please let me know how we get rid of last charactor from the following
values using TSQL?

0294S
0324S
0341S
0362S
0378S
0398S
0436S
0456S
0506S
0542S
0561S
0583S
0599S
0620S
0639S
0657S
0684S
0707S
0720S
0748S
0777S
0796S
0817S
0845S
0862S
0890S
0900S
0936S
4107S
4150S
4357S
4441S
4461S
4476S
4495S
4511S
4528S
4561S
4581S
4598S
4933S
4951S
5757S
7629S
7791S
7880S
8712S
8760S
5758S
5759S
5769S
5771S
5773S
5776S
5777S
7645S
7646S
7647S
7656S
7658S
7659S
7730S
7792S
7793S
7794S
7795S
7796S
7797S
7798S
7881S
7889S
7890S
7891S
7892S
7893S
7894S
8713S
8714S
8715S
8721S
8726S
8727S
8731S
8761S
8769S
8770S
4116S
4118S
4119S
7591S
4123S
4124S
4125S
5733S
4155S
4161S
4167S
4301S
4308S
4312S
4360S
4361S
4362S
4365S
8737S
4370S
4372S
4442S
4443S
4444S
4446S
4447S
4449S
4450S
4462S
4464S
4467S
4471S
4472S
4473S
4474S
4477S
4478S
4479S
4480S
4481S
4482S
4483S
4496S
4497S
4498S
4499S
4500S
4501S
4502S
4512S
4513S
4514S
4515S
4516S
4517S
4518S
4530S
4531S
4532S
4533S
4534S
4535S
4536S
4562S
4563S
4564S
4567S
4568S
4570S
4571S
4582S
4583S
4584S
4587S
4595S
4596S
4597S
4600S
4605S
4619S
4630S
4551S
7565S
4642S
0640S
0641S
0642S
0643S
0645S
0646S
0647S
4934S
4935S
4936S
4937S
4939S
4940S
4941S
0658S
0660S
0661S
0662S
0663S
0665S
0667S
0685S
0686S
0688S
0690S
0691S
0693S
0694S
0709S
0710S
0711S
0713S
0716S
0717S
0719S
0723S
0724S
0725S
0726S
0728S
0730S
0731S
0749S
0750S
0751S
0752S
0754S
0757S
0760S
0778S
0779S
0780S
0781S
0782S
0783S
0784S
0797S
0798S
0800S
0801S
0802S
0803S
0804S
4952S
4953S
4956S
4957S
5714S
5720S
5721S
0819S
0820S
0821S
0822S
0823S
0824S
0825S
0846S
0847S
0848S
0849S
0850S
0851S
0852S
0864S
0865S
0866S
0869S
0872S
0874S
0878S
0891S
0893S
0894S
0896S
0897S
0898S
0899S
0295S
0296S
0297S
0298S
0299S
0302S
0303S
0901S
0903S
0904S
0905S
0906S
0908S
0909S
0325S
0327S
0328S
0329S
0330S
0331S
0332S
0342S
0343S
0344S
0345S
0347S
0348S
0350S
0363S
0364S
0365S
0368S
0370S
0371S
0375S
0379S
0380S
0381S
0382S
0383S
0384S
0385S
0399S
0401S
0403S
0404S
0406S
0408S
0414S
0437S
0439S
0440S
0442S
0444S
0445S
0446S
0458S
0459S
0460S
0461S
0462S
0463S
0466S
0938S
0939S
0942S
0946S
0949S
0950S
0951S
0508S
0510S
0511S
0512S
0514S
0516S
0518S
0544S
0545S
0546S
0547S
0548S
0549S
0550S
0562S
0563S
0565S
0566S
0569S
0570S
0571S
0585S
0591S
0592S
0594S
0595S
0596S
0597S
0600S
0601S
0602S
0603S
0604S
0605S
0606S
0621S
0622S
0623S
0625S
0626S
0627S
0628S
0306S
0333S
0352S
0387S
0418S
0447S
0471S
0519S
0528S
0552S
0573S
0608S
0629S
0648S
0668S
0676S
0695S
0735S
0761S
0785S
0805S
0826S
0836S
0853S
0879S
0911S
0952S
7555S
4651S
4344S
4401S
4451S
4485S
4503S
4519S
4537S
4549S
4572S
4643S
4942S
5726S
5778S
7731S
7781S
7799S
7897S
8736S
7782S
7783S
7784S
7785S
7786S
7787S
7789S
7804S
7818S
7831S
7838S
7847S
7853S
7854S
7898S
7899S
7917S
7579S
7935S
7994S
4670S
4671S
8739S
8741S
8745S
8750S
8757S
8758S
8759S
4345S
4435S
4419S
8732S
4350S
4418S
4674S
4404S
4405S
4406S
4407S
4409S
4411S
4440S
4452S
4453S
4454S
4455S
4456S
4458S
4459S
4487S
4489S
4490S
4491S
4492S
4493S
4494S
4504S
4505S
4506S
4507S
4508S
4509S
4510S
4520S
4522S
4523S
4524S
4525S
4526S
4527S
4539S
4540S
4541S
4544S
4545S
4546S
4547S
4550S
4553S
4554S
4555S
4556S
4559S
4560S
4573S
4575S
4576S
4577S
4578S
4579S
4580S
4644S
4921S
4924S
4926S
4928S
4929S
4931S
4943S
4944S
4945S
4947S
4948S
4949S
4950S
5730S
5749S
5750S
5752S
5753S
5754S
5755S
7505S
7523S
7541S
7562S
7602S
7606S
7621S
0677S
0678S
0679S
0680S
0681S
0682S
0683S
0696S
0697S
0699S
0702S
0703S
0704S
0706S
0737S
0739S
0740S
0741S
0744S
0745S
0746S
0762S
0768S
0770S
0772S
0774S
0775S
0776S
0786S
0787S
0788S
0790S
0791S
0792S
0793S
0806S
0807S
0808S
0809S
0811S
0812S
0813S
0827S
0829S
0831S
0832S
0833S
0834S
0835S
7735S
7739S
7774S
7777S
7778S
7779S
7780S
0838S
0839S
0840S
0841S
0842S
0843S
0844S
0854S
0856S
0857S
0858S
0859S
0860S
0861S
0880S
0882S
0883S
0884S
0885S
0886S
0889S
0920S
0923S
0924S
0925S
0929S
0931S
0934S
0953S
0970S
0989S
0991S
0992S
0993S
4104S
4129S
4133S
4136S
4141S
4142S
4143S
4146S
0334S
0335S
0336S
0337S
0338S
0339S
0340S
0353S
0355S
0356S
0357S
0358S
0360S
0361S
0388S
0390S
0391S
0392S
0394S
0395S
0396S
0419S
0420S
0423S
0429S
0430S
0433S
0435S
0448S
0450S
0451S
0452S
0453S
0454S
0455S
0472S
0475S
0499S
0501S
0503S
0504S
0505S
0520S
0522S
0523S
0524S
0525S
0526S
0527S
4327S
4332S
4333S
4337S
4338S
4340S
8729S
0530S
0536S
0537S
0538S
0539S
0540S
0541S
0553S
0554S
0556S
0557S
0558S
0559S
0560S
0574S
0575S
0577S
0579S
0580S
0581S
0582S
0609S
0611S
0614S
0615S
0616S
0617S
0618S
0285S
0287S
0289S
0290S
0291S
0292S
0293S
0630S
0631S
0632S
0635S
0636S
0637S
0638S
0649S
0650S
0651S
0652S
0653S
0654S
0656S
0669S
0670S
0671S
0672S
0673S
0674S
0675S
0307S
0310S
0311S
0312S
0313S
0314S
0322S
4435R
38R
4664R
1821R
42R
43R
44R
3087R
3562R
3596R
3598R
3600R
3604R
3606R
3608R
3610R
3612R
3614R
3616R
3912R
4202R
4204R
4206R
4671R
64R
5345R
5375R
5376R
5342R
5341R
5339R
5338R
5336R
5337R
5340R
5335R
5352R
5356R
75R
2089R
3036R
3504R
3505R
3511R
3504R
3513R
3521R
3520R
3532R
3534R
3570R
3734R
3795R
3798R
3799R
3800R
3801R
3802R
3811R
3812R
4997R
4580R
3813R
5492R
3712R
3704R
5263R
5348R
3681R
5288R
5350R
5372R
1500X
1501X
1502X
1503X
1504X
1505X
1506X
1507X
1508X
1509X
1510X
1511X
1513X
1514X
1515X
1517X
1526X
1527X
1528X
1529X
1530X
1531X
1532X
1533X
1534X
1535X
1536X
1537X
1538X
1539X
1540X
1541X
1542X
1543X
1544X
1545X
1546X
1547X
1549X
1550X
1551X
1552X
1553X
1554X
1555X
1556X
1557X
1558X
1559X
1560X
1561X
1562X
1563X
1565X
1566X
1567X
1568X
1569X
1570X
1572X
1573X
1574X
1575X
1576X
1577X
1578X
1579X
1580X
1581X
1582X
1583X
1584X
1585X
1587X
1588X
1589X
1591X
1593X
1594X
1595X
1596X
1597X
1598X
1600X
1601X
1602X
1603X
1604X
1605X
1606X
1607X
1608X
1609X
1610X
1611X
1612X
1613X
1614X
1615X
1616X
1617X
1618X
1619X
1620X
1621X
1622X
1623X
1624X
1625X
1626X
1627X
1628X
1629X
1630X
1631X
1632X
1633X
1634X
1635X
1636X
1637X
1638X
1639X
1640X
1641X
1643X
1644X
1645X
1646X
1647X
1648X
1649X
1650X
1651X
1652X
1653X
1654X
1655X
1656X
1657X
1658X
1659X
1660X
1661X
1662X
1663X
1664X
1665X
1666X
1667X
1668X
1669X
1670X
1671X
1672X
1673X
1674X
1675X
1676X
1678X
1679X
1680X
1681X
1682X
1683X
1684X
1685X
1686X
1687X
1688X
1689X
1690X
1691X
1692X
1694X
1695X
1696X
1697X
1698X
1700X
1701X
1702X
1703X
1704X
1705X
1707X
1708X
1709X
1710X
1711X
1713X
1717X
1720X
1721X
1722X
1723X
1724X
1725X
1726X
1727X
1728X
1729X
1730X
1731X
1732X
1733X
1734X
1735X
1736X
1737X
1741X
1743X
1744X
1745X
1746X
1747X
1751X
1752X
1753X
1754X
1755X
1756X
1757X
1758X
1759X
1760X
1761X
1762X
1763X
1764X
1765X
1766X
1767X
1768X
1770X
1771X
1772X
1773X
1774X
1775X
1776X
1777X
1778X
1779X
1780X
1781X
1782X
1783X
1784X
1785X
1786X
1788X
1789X
1790X
1791X
1792X
1793X
1794X
1795X
1797X
1798X
1799X
1800X
1801X
1802X
1803X
1804X
1805X
1806X
1807X
1808X
1809X
1810X
1811X
1812X
1813X
1814X
1815X
1816X
1817X
1818X
1819X
1820X
1821X
1822X
1823X
1824X
1825X
1826X
1827X
1828X
1829X
1830X
1831X
1832X
1833X
1834X
1835X
1836X
1837X
1838X
1839X
1840X
1841X
1842X
1843X
1844X
1845X
1846X
1847X
1848X
1849X
1850X
1851X
1853X
1854X
1855X
1856X
1858X
1859X
1860X
1861X
1862X
1864X
1865X
1866X
1867X
1868X
1869X
1870X
1871X
1873X
1874X
1875X
1876X
1877X
1878X
1879X
1881X
1882X
1900X
1901X
1902X
1903X
1904X
1905X
1906X
1907X
1908X
1909X
1910X
1911X
1912X
1913X
1914X
1915X
1916X
1917X
1919X
1920X
1921X
1922X
1923X
1924X
1925X
1926X
1928X
1929X
1931X
1932X
1933X
1934X
1935X
1936X
1937X
1938X
1939X
1940X
1942X
1943X
1944X
1945X
1946X
1947X
1948X
1949X
1950X
1951X
1953X
1954X
1955X
1956X
1957X
1959X
1960X
6700X
6701X
6702X
6703X
6704X
6705X
6706X
6707X
6708X
6709X
6710X
6711X
6712X
6713X
6714X
6715X
6716X
6717X
6718X
6719X
6720X
6721X
6722X
6723X
6724X
6725X
6726X
6727X
6728X
6729X
6730X
6731X
6732X
6733X
6734X
6735X
6736X
6737X
6738X
6739X
6740X
6741X
6742X
6743X
6744X
6745X
6746X
6747X
6748X
6749X
6750X
6751X
6752X
6753X
6754X
6755X
6756X
6757X
6758X
6759X
6760X
6761X
6762X
6763X
6764X
6765X
6766X
6767X
6768X
6769X
6770X
6771X
6772X
6773X
6774X
6776X
6778X
6779X
6780X
6782X
6792X
6793X
6794X
6795X
6796X
6797X
6798X
6799X
6800X
6801X
6802X
6803X
6804X
6805X
6806X
6807X
6808X
6809X
6810X
6811X
6812X
6813X
6814X
6815X
6816X
6817X
6818X
6819X
6820X
6821X
6822X
6823X
6824X
6825X
6826X
6828X
6829X
6830X
6831X
6832X
6833X
6834X
6835X
6836X
6838X
6839X
6840X
6841X
6842X
6843X
6844X
6845X
6846X
6847X
6848X
6849X
6851X
6852X
6853X
6854X
6855X
6859X
6860X
6861X
6862X
6863X
6865X
6866X
6867X
6868X
6869X
6870X
6871X
6873X
6875X
6876X
6877X
6878X
6879X
6880X
6882X
6883X
6884X
6892X
6900X
6901X
6902X
6903X
6904X
6905X
6906X
6907X
6908X
6909X
6910X
6912X
6913X
6914X
6915X
6916X
6917X
6918X
6919X
6920X
6921X
6922X
6923X
6924X
6925X
6926X
6927X
6928X
6929X
6930X
6931X
6932X
6933X
6934X
6935X
6936X
6937X
6938X
6939X
6941X
6942X
6943X
6944X
6945X
6946X
6947X
6948X
6949X
6950X
6951X
6952X
6953X
6954X
6956X
6957X
6959X
6960X
6961X
6962X
6963X
6964X
6966X
6967X
6969X
6970X
6971X
6972X
1076K
1102K
1058K
1098K
1169K
1036K
1155K
1100K
1075K
1123K
1079K
1077K
1087K
1130K
1064K
1042K
1086K
1128K
1050K
1093K
1046K
1097K
1072K
1129K
1074K
1054K
1165K
1002K
1084K
1160K
1019K
1182K
1141K
1399K
1113K
1030K
1094K
1127K
1146K
1213K
1189K
1078K
1397K
1006K
1134K
1055K
1099K
1066K
1131K
1034K
1031K
1032K
1186K
1133K
1013K
1067K
1159K
1125K
1132K
1179K
1145K
1027K
1140K
1060K
1150K
1120K
1047K
1108K
1148K
1149K
1101K
1017K
1062K
1124K
1029K
1063K
1177K
1135K
1122K
1159K
1056K
1033K
1041K
1166K
1096K
1162K
1187K
1158K
1181K
1018K
1139K
1143K
1115K
1024K
1119K
1057K
1088K
1052K
1142K
1004K
1039K
1103K
1022K
1012K
1003K
1005K
1153K
1167K
1048K
1151K
1116K
1170K
1121K
1021K
1059K
1104K
1049K
1161K
1110K
1035K

Thanks for help
0
Comment
Question by:keplan
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
Assuming all the values are 4 numbers then a letter.

update mytable set mycolumn = left(mycolumn, 4)
0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
SELECT LEFT(FieldName,LEN(FieldName)-1)

If you want to actually change the values, you can do:
UPDATE TableName
SET FieldName=LEFT(FieldName,LEN(FieldName)-1)
0
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
If they are arbitrary length:

update mytable set mycolumn = left(mycolumn, len(mycolumn) - 1)


If the value has leading or trailing spaces:

update mytable set mycolumn = left(ltrim(rtrim(mycolumn)), len(ltrim(rtrim(mycolumn))) - 1)
0
 
LVL 6

Expert Comment

by:Peter Kiprop
Comment Utility
All the above are correct. probably you may only want to return values on querry and not update the field then use the below

 SElect left(ltrim(rtrim(mycolumn)), len(ltrim(rtrim(mycolumn))) - 1) as Mycolumn  from TableName
0
 

Author Comment

by:keplan
Comment Utility
Hi,

I get the following error message when I run your code; why is that?

Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 21

Expert Comment

by:Alpesh Patel
Comment Utility
SElect left(ltrim(rtrim(mycolumn)), len(ltrim(rtrim(mycolumn))) - 1) as Mycolumn  from TableName


With this check not NULL and LEN > 0
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
I get the following error message when I run your code; why is that?
That would be because not all the values are 5 bytes.  Hence "Assuming all the values are 4 numbers then a letter."
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
Comment Utility
Since they are not all 5 bytes (e.g. 42R, 38R, 43R, 44R, 64R) you will have to use the suggestion here http:#a39224885
0
 

Author Closing Comment

by:keplan
Comment Utility
c
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Cluskitt,

I do apologize, as you can imagine I had no intention of stealing your points.  I assumed that the author could follow basic English and would know that no points were merited in my case.  I would ask an EE Moderator to intervene and re-open this question unfortunately in the past I have been told that they must abide by the author's decision.
0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
It's not really important. I'm not in this for the points. As long as the OP solved his problem, I'm good. Thanks anyway.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now