help

how can i get the result .....A B
JOHN N
OHN HN
HN OHN
N JOHN
where JOHN is an employee in emp table...
Message posted via http://www.droptable.com
The example below uses a derived table containing numbers up to the maximum
size for the FirstName column. You might consider creating a general
purpose permanent table if this is the sort of thing you do often.
SELECT
SUBSTRING(FirstName, Position, LEN(FirstName) - Position + 1) AS A,
RIGHT(FirstName, Position) AS B
FROM dbo.emp
CROSS JOIN (
SELECT 1 AS Position
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10 AS Position) AS Positions
WHERE
FirstName = 'JOHN' AND
Position <= LEN(FirstName)
Hope this helps.
Dan Guzman
SQL Server MVP
"Nishu via droptable.com" <u30587@.uwe> wrote in message
news:6d33e7a43e7ba@.uwe...
> how can i get the result .....A B
> JOHN N
> OHN HN
> HN OHN
> N JOHN
>
>
> where JOHN is an employee in emp table...
> --
> Message posted via http://www.droptable.com
>
|||This assumes a table of integers named Numbers with values that at
least cover the range from 1 to the maximum length of the name.
SELECT RIGHT(E.employee, LEN(E.employee) - N.Nbr + 1),
RIGHT(E.employee, N.Nbr)
FROM Emp as E
JOIN Numbers as N
ON N.Nbr between 1 and LEN(E.employee)
Roy Harvey
Beacon Falls, CT
On Fri, 02 Feb 2007 12:18:38 GMT, "Nishu via droptable.com"
<u30587@.uwe> wrote:

>how can i get the result .....A B
> JOHN N
> OHN HN
> HN OHN
> N JOHN
>
>
>where JOHN is an employee in emp table...
|||thanx roy,it worked well..
Roy Harvey wrote:[vbcol=seagreen]
>This assumes a table of integers named Numbers with values that at
>least cover the range from 1 to the maximum length of the name.
>SELECT RIGHT(E.employee, LEN(E.employee) - N.Nbr + 1),
> RIGHT(E.employee, N.Nbr)
> FROM Emp as E
> JOIN Numbers as N
> ON N.Nbr between 1 and LEN(E.employee)
>Roy Harvey
>Beacon Falls, CT
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com
|||thnx dan
Dan Guzman wrote:[vbcol=seagreen]
>The example below uses a derived table containing numbers up to the maximum
>size for the FirstName column. You might consider creating a general
>purpose permanent table if this is the sort of thing you do often.
>SELECT
> SUBSTRING(FirstName, Position, LEN(FirstName) - Position + 1) AS A,
> RIGHT(FirstName, Position) AS B
>FROM dbo.emp
>CROSS JOIN (
> SELECT 1 AS Position
> UNION ALL SELECT 2
> UNION ALL SELECT 3
> UNION ALL SELECT 4
> UNION ALL SELECT 5
> UNION ALL SELECT 6
> UNION ALL SELECT 7
> UNION ALL SELECT 8
> UNION ALL SELECT 9
> UNION ALL SELECT 10 AS Position) AS Positions
>WHERE
> FirstName = 'JOHN' AND
> Position <= LEN(FirstName)
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com