Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cplusplus
Aged Yak Warrior
567 Posts |
 Posted - 2010-07-17 : 06:54:51
|
| Is it possible to get rightside last char.select right_side_last_char(uname) from tab_Unames where uname like @Uname+'%'if the right side char isnumeric then true otherwise false is it possible? |
|
|
beniaminus
Starting Member
21 Posts |
Posted - 2010-07-17 : 07:44:33
|
| You could create a stored procedure or a function to do it. You'd have to input the user name and output 0 or 1.It could be done a bit like this:DECLARE @char NVARCHAR(1)SET @char = RIGHT(@uname, 1) IF PATINDEX('%[0-9]%',@uname) > 0 return 1ELSE return 0----------------------------------------@uname is the username. It would be passed to the function.PATINDEX is a string function. It looks for a pattern and returns the position of that pattern in the string. In this case its looking for any number.Hope this helps,Ben :) |
 |
|
|
cplusplus
Aged Yak Warrior
567 Posts |
Posted - 2010-07-17 : 08:19:31
|
| How about if the username has a number after teh username, that is greater than 9.if username has jsmith12Is there a way to get what number is trailing after the username. |
|
|
|
beniaminus
Starting Member
21 Posts |
Posted - 2010-07-17 : 09:14:13
|
| Well, at the moment it just selects the last character. So in this case it would see the number 2. You could use use the PATINDEX function to find the position of the first number in the username, then find out the length of the username and subtract the length from the position.Something like this:DECLARE @char NVARCHAR(10)DECLARE @pos INTSET @pos = PATINDEX('%[0-9]%',@uname) - 1 -- Find the position of the first number in the usernameSET @char = RIGHT(@uname, (LEN(@uname) - @pos)) -- Take out the numberIF PATINDEX('%[0-9]%',@char) > 0return 1ELSEreturn 0Bear in mind that this would only work if the number was always at the end of the user name. If the username was 12jsmith or it would return the whole username, and if the username was jsm1th12 it would return "1th12".P.S. I just realised the code in my first reply was wrong. The IF statement should have @char instead of @unameBen :) |
|
|
|
cplusplus
Aged Yak Warrior
567 Posts |
Posted - 2010-07-17 : 10:45:01
|
Thank you very much Ben...quote:
Originally posted by beniaminus
Well, at the moment it just selects the last character. So in this case it would see the number 2. You could use use the PATINDEX function to find the position of the first number in the username, then find out the length of the username and subtract the length from the position.Something like this:DECLARE @char NVARCHAR(10)DECLARE @pos INTSET @pos = PATINDEX('%[0-9]%',@uname) - 1 -- Find the position of the first number in the usernameSET @char = RIGHT(@uname, (LEN(@uname) - @pos)) -- Take out the numberIF PATINDEX('%[0-9]%',@char) > 0return 1ELSEreturn 0Bear in mind that this would only work if the number was always at the end of the user name. If the username was 12jsmith or it would return the whole username, and if the username was jsm1th12 it would return "1th12".P.S. I just realised the code in my first reply was wrong. The IF statement should have @char instead of @unameBen :)
|
|
|
|
beniaminus
Starting Member
21 Posts |
Posted - 2010-07-17 : 23:06:21
|
| No problem my friend.Ben :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-18 : 02:29:07
|
| Note that this doesn't work with embedded digits, you can use REVERSE() to work around that:SELECT PATINDEX('%[0-9]%', 'jsmith12')SELECT PATINDEX('%[0-9]%', 'j9smith12')SELECT PATINDEX('%[^0-9]%', REVERSE('j9smith12'))SELECT RIGHT('j9smith12', PATINDEX('%[^0-9]%', REVERSE('j9smith12'))-1) |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Find rightside last char using select